Count instances of comma in a string

I have strings like this:
"236 A 100, 236 A 100 B, A 100, A 100 R, A 100 S"

I would like to filter in access query grid for strings containing more than two comma's.

So above would get shown:

"236 A 100, 236 A 100 B" this would not.
PeterBaileyUkAsked:
Who is Participating?
 
clarkscottConnect With a Mentor Commented:
I'd create a function.  This function splits your text into "sections".  You can count the sections. Since the array will start at zero, the numer that's actually returned should be the correct count.  
Put this in a query along with your text field to examine and query for the number of commas you're looking for. It will return a number.

Function CountCommas (byref YourField as string) as long
dim vArray () as string

vArray = Split(YourField, ",")
CountCommas = ubound(varray)

End Function


You query:  CommaCount:  = CountCommas([YourField])

if CommaCount > 2 then - do something.


Scott C
0
 
Dale FyeConnect With a Mentor Commented:
easiest way is something like:

CommaCount: Len([yourField] & "") - LEN(Replace([YourField] & "", ",", ""))
Criteria: > 2

This simply counts the number of characters in the string, and then the number of characters in the string after having replaced all of the commas with a zero length string.

I tend to add the:

& ""

inside all of my string function calls to avoid the possibility that the function will raise an error if a NULL is passed to it.
0
 
Kalpesh ChhatralaConnect With a Mentor Software ConsultantCommented:
you can count  like below

Function CntCharacterInString(strToSrch As String, strCharToCount As String) As Long
   Dim lPos As Long
   Dim lnTotal As Long
   For lPos = 1 To Len(strToSrch)
      If Mid$(strToSearch, lPos, Len(strCharToCount)) = strCharToCount Then
         lnTotal = lnTotal + 1
      End If
   Next
   CntCharacterInString = lnTotal
End Function

Open in new window

0
 
PeterBaileyUkAuthor Commented:
thank you all equally valid, so shared
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.