Count commas in a field

[ScottXe]

I have one field containing a couple of 6 digit codes and would like to count the number of codes in the field and write the number to a calculated field. Each code is separated by a comma and a space. How can I count the no of comma within the field? Thanks!

 

[MarkK]

Probably the least amount of code is to use Split(), which returns an array, and then count the array elements using UBound().

Function CountChars(Text as string, Char as string) as Integer
   CountChars = UBound(Split(Text, Char))
End Function

 

[ScottXe]

Hi MarkK,

I am unsure how to apply it to the query. Could you give me more guidance. Thanks!

 

[MarkK]

Add a field to the query with text like . . .

CountChars: UBound(Split([FieldToSplit], ", "))

This displays the count in a new field. I wouldn't store that value though. Just always calculate it as required.