count commas in a text field in Access

[SeaGuy]

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.

[Janos™]

The easiest way that I know is to use the Split function, take a look at the below example:

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

[SeaGuy]

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.

[JAMES_911]

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.

[SeaGuy]

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