Expression for counting values in a field separated by commas...

[David618]

I need help with an expression. I have a table that has a text field with values separated by commas (the field name is Cert_Otr and it contains certifications separated by commas - e.g., "PMP, CHC" or "RHIT, RN, CFE"). In my expression, I need it to count the number of those. So, for instance, if the value is "PMP, CHC" it needs to return "2" - or "RHIT, RN, CFE" it needs to return "3". If the field is empty it needs to return "0".

What's the best way to do this in a query using just the built in functions?

[Robeen]

I'm not TOTALLY certain - but I think you will have to create a VBA function - and create a loop to search through the string for the number of commas.
You could use the InStr(PositionToSearchFrom, StringToSearch, "CharacterToFindInString") function to find the first comma - but to find ALL the commas - you will have to increment the position from which the InStr() function starts looking for subsequent commas in the field. That's why you need a Loop - and hence, a VBA function.
You can call the VBA Function from your Query.
It's not difficult. Let us know if you want to try it!

[David618]

Yeah, that's kind of what I was thinking - something to count all of the commas. Thought it would be *easy* using built in functions, but discovering that it's not-so-easy via that method. It's one of those situations where you think, "surely there's a built-in function that can do this." Then you search and can't find anything, so I tabled it to deal with it later, now I'm having to address it!

Sure, I'm willing to give it a "college" try using some VBA, but I have to caution you that I'm pretty new to it. I'm not totally ignorant - I know how to get to the VBA editor!

[Robeen]

Ok - try this:

1. Create a Function in your VBA Editor - like this:

Function CountCommas(Cert_OTR As String) As Integer
CountCommas = 5
End Function

In your Query design view - create a new field like this:
Commas: CountCommas([Cert_OTR])
Run the Query & you'll see a 5 in every row of the Comma field. That's because the function is not counting anything yet. It is just returning the number 5 to the query.

That is just to give you the framework of how the query & Function work together.

Next, you can DO something with the Cert_OTR string that your Query passes to the Function:

Function CountCommas(Cert_OTR As String) As IntegerDim intPos As Integer, intPos2 as Integer, intCount as Integer

intPos1 = 1

intPos2 = InStr(intPos1, Cert_OTR, ",")
CountCommas = intPos

End Function

What you'll get from that is the position of the first comma in the string [if there is a comma].

Finally, you will need to add code to do a few other things in a loop:
You could create a
Do While intPos > 0
'put all your code in here.
'when there are no commas - the InStr function returns 0.
Loop

1. Determine if there is anything at all in the string you are passing [use the Len() VBA function]. If so - set a 'counter' variable to 1.
2. If there IS text in there - but you don't find a comma - does that mean there is only one value in the field? If so do a CountCommas = 1.
3. If you DO find a comma:
a. Increment the comma-counter.
b. Increment intPos by 1 [intPos + 1]
c. Loop around to run the InStr function again . . .

Hope this helps get you started - but let us know if you need more help!!
This is just meant to give you something to start off with . . .

All the Best

[June7]

Try something like:

Function CountElements(strString As String) As Integer
Dim aryA As Variant
aryA = Split(strString)
CountElements = UBound(aryA) + 1
End Function


Now Call the Function

CountElements([fieldname])

Note that the function will error if [fieldname] is Null.

[amrut]

Does this helps ?
1. Get length of your field without spaces : Len(Replace([Cert_Otr], " ", ""))
2. Get length of your field without spaces and commas : Len(Replace(Replace([Cert_Otr], " ", ""), ",", ""))
The subtraction of 1 and 2 above will give you number of commas. Your required count will be the result of subtraction +1 .Use your logic accordingly.

[Robeen]

June,

This works [change to your code in red] to give a count of elements. Without this - the function errors for rows with no data [like you said] and returns 1 for fields WITH data.

Function CountElements(strString As String) As Integer
Dim aryA As Variant
aryA = Split(strString,",")
CountElements = UBound(aryA) + 1
End Function

I like this a lot better than what I was suggesting! Thanks.

[June7]

My code worked when I tested, space is default separator. But good point to specify comma as spaces might not be consistent, take for granted that commas are. Null still will cause error.

Very clever amrut, I like. This can be done without VBA. However, will also error if [Cert_Otr] is Null.

SELECT *, Len(Replace([Cert_Otr], " ", "")) - Len(Replace(Replace([Cert_Otr], " ", ""), ",", "")) + 1 AS CountElements FROM tablename;

[amrut]

June,
thanks for the compliment.
The error on NULL value can be checked using an IIF(Len([Cert_Otr])=0,0,Len(Replace([Cert_Otr], " ", "")) - Len(Replace(Replace([Cert_Otr], " ", ""), ",", ""))+1) as CountElements

[June7]

Actually, that will still error because all parts of IIf must be able to evaluate, even if it is not the part returning result. Also, Len(Null) = Null which does not = 0 and condition fails. So handle possible Null with Nz.

IIF(Len(Nz([Cert_Otr],""))=0, 0, Len(Replace(Nz([Cert_Otr].""), " ", "")) - Len(Replace(Replace(Nz([Cert_Otr],""), " ", ""), ",", "")) + 1)

[David618]

Wow! Thanks everyone! I left for a meeting for a couple of hours and came back to be the beneficiary of all of these great ideas. I'll go back and try some of them and tell you which I like best - thank you!!!