Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
Solved

# Count instances of comma in a string

Posted on 2014-03-25
Medium Priority
761 Views
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.
0
Question by:PeterBaileyUk
[X]
###### Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points

LVL 20

Accepted Solution

clarkscott earned 668 total points
ID: 39952787
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

LVL 48

Assisted Solution

Dale Fye earned 668 total points
ID: 39952821
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.

& ""

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

LVL 16

Assisted Solution

Kalpesh Chhatrala earned 664 total points
ID: 39952838
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
``````
0

Author Closing Comment

ID: 39952850
thank you all equally valid, so shared
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
###### Suggested Courses
Course of the Month7 days, 6 hours left to enroll