Nokia C3: count commas in a text field in Access

count commas in a text field in Access
Hi there.

I'm working with an Access database and need to be able to count the number of commas (or comma separated words) in a text field in the table [Survey Data]. The field has numerous comma separated words.

I've read a bit online that I could parse the field into multiple fields, or could possibly use CountCSWords to count the number of comma separated words. I don't see the CountCSWords function in the Expression Builder though. I'm pretty new (actually, very new) to Access though and have no idea how to do this. Is there a way that I can have this as an expression in a calculated field in a table or query? I'd really appreciate any help y'all can give me so I can get this t work.
Reply With Quote
#2
Old 07-09-2011
Janos™ Janos™ is offline
Member

Join Date: Jan 2006
Posts: 327
Re: count commas in a text field in Access
The easiest way that I know is to use the Split function, take a look at the below example:

Code:

Public Function CountStringItems( _
Items As String, _
Delim As String _
) As Long
Dim varaItems As Variant
varaItems = Split(Items, Delim)
CountStringItems = ArrayCount(varaItems)
End Function

Public Function ArrayCount(varArray As Variant)
ArrayCount = UBound(varArray) - LBound(varArray) + 1
End Function

Reply With Quote
#3
Old 07-09-2011
SeaGuy SeaGuy is offline
Member

Join Date: Sep 2011
Posts: 3
Re: count commas in a text field in Access
Thanks!

Where would I paste this code? In VB? And then how to I reference it in a table or query? Sorry, this is pretty new to me.
Reply With Quote
#4
Old 07-09-2011
JAMES_911's Avatar
JAMES_911 JAMES_911 is offline
Member

Join Date: Dec 2007
Posts: 1,611
Re: count commas in a text field in Access
Well, I think that you will need to apply the code in two events of the form - The On Current Event of the Form and the On Change Event of the Textbox that you are verifying the data entry of. When building a query in Microsoft Access, you can use expressions to perform calculations with your data. The "Len" expression will count the number of characters in a specific field of your Access database file.
Reply With Quote
#5
Old 07-09-2011
SeaGuy SeaGuy is offline
Member

Join Date: Sep 2011
Posts: 3
Re: count commas in a text field in Access
Ah, okay. Thanks - I'm starting to get it now.

So basically, it's not possible to do what I want as a calculated field in a table or query - I'd have to do it in a form, right?

Last question, I promise
Reply With Quote