How to Count the Number of Specific Character in a Field 1

[TheTeaMan]

I have string data in one of my fields, separated by “,”. I would like to count how many times the “,” occurred in the field. If none, then I need to put 1.
Would you help please! Thanks..

 

[lameid]

A way would be to convert the data to an array based on comma separators and find the highest element in the array...

Ubound(Split([i]<Test Value>[/i],","))

Of course you need to replace <Test Value> with your value whether that is a field in a query or a variable in code.

The other way would be to write a function to do this. I have no idea which would ultimately be faster. If it is literally a value then I would just use the method above. If you are using it on thousands of rows in a query, it may make a noticable difference.

 

[PHV]

yourSubFieldsCount: 1+Len([your field])-Len(Replace([your field],',',''))

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[HarleyQuinn]

If you do want to use a user defined function (and a different approach than lameid posted), you could use something like (will work with , by default but you can specify others to count):

Function CountChar(strField As String, Optional CharToCount As String = ",") As Integer
Dim re As Object
Dim mc As Object

If InStr(1, "[\^$.|?*+(){}", CharToCount) > 0 Then CharToCount = "\" & CharToCount

Set re = CreateObject("VBScript.RegExp")

With re
    .Global = True
    .MultiLine = True
    .IgnoreCase = True
    .Pattern = CharToCount
    Set mc = .Execute(strField)
    
End With

If mc.Count > 0 Then
CountChar = mc.Count
Else
CountChar = 1
End If

Set mc = Nothing
Set re = Nothing

End Function

Hope this helps

HarleyQuinn
---------------------------------
Carter, hand me my thinking grenades!

You can hang outside in the sun all day tossing a ball around, or you can sit at your computer and do something that matters. - Eric Cartman

Get the most out of Tek-Tips, read FAQ222-2244: How to get the best answers before posting.

http://lessthandot.com

 

[lameid]

I vote for PHV's solution.

 

[HarleyQuinn]

I would agree, except it doesn't return correctly if there's more than 0 comma's in the field...

HarleyQuinn
---------------------------------
Carter, hand me my thinking grenades!

You can hang outside in the sun all day tossing a ball around, or you can sit at your computer and do something that matters. - Eric Cartman

Get the most out of Tek-Tips, read FAQ222-2244: How to get the best answers before posting.

http://lessthandot.com

 

[TheTeaMan]

Thanks to all. PHV's solution was the best for me. It was straight forward. Thank you again.

 

[HarleyQuinn]

I suppose if you're happy with an incorrect solution (for the original question at least) then more power to you...

HarleyQuinn
---------------------------------
Carter, hand me my thinking grenades!

You can hang outside in the sun all day tossing a ball around, or you can sit at your computer and do something that matters. - Eric Cartman

Get the most out of Tek-Tips, read FAQ222-2244: How to get the best answers before posting.

http://lessthandot.com

 

[TheTeaMan]

Thank you HarleyQuinn for your response. I did add IIF statement for 0 comma's in the field.

 

[lameid]

Oops I missed the OP requirement...

If none, then I need to put 1.

My solution and PHV's both fundamentally count the commas. PHV adds one which effectively yields the number elements separated by commas.

To do what the OP states and incorporating PHV's method which is better than mine...

yourSubFieldsCount: IIF(instr(1,"Test",","),1, Len([your field])-Len(Replace([your field],',','')) )

 

[HarleyQuinn]

Thanks for the reply TeaMan.

Could you show us (just for the completeness of the thread, so people finding it in a search will see a complete solution)?

HarleyQuinn
---------------------------------
Carter, hand me my thinking grenades!

You can hang outside in the sun all day tossing a ball around, or you can sit at your computer and do something that matters. - Eric Cartman

Get the most out of Tek-Tips, read FAQ222-2244: How to get the best answers before posting.

http://lessthandot.com

 

[lameid]

I thought I had the = 0 in the truepart of the IIF... Anyway this is actually right.

yourSubFieldsCount: IIF(instr(1,"Test",",") = 0 ,1, Len([your field])-Len(Replace([your field],',','')) )

 

[HarleyQuinn]

Thanks lameid, that's what I was trying to prod us along to

HarleyQuinn
---------------------------------
Carter, hand me my thinking grenades!

You can hang outside in the sun all day tossing a ball around, or you can sit at your computer and do something that matters. - Eric Cartman

Get the most out of Tek-Tips, read FAQ222-2244: How to get the best answers before posting.

http://lessthandot.com

 

[lameid]

Sadly though I didn't catch my other mistake until it was staring me in the face again... I didn't substitute the field for the test I used so it is consistent.

yourSubFieldsCount: IIF(instr(1,[your field],",") = 0 ,1, Len([your field])-Len(Replace([your field],',','')) )

Here is hoping I have all mistakes out of the way for the day!
I have already exceeded quota and all in the same place.

 

[HarleyQuinn]

HarleyQuinn
---------------------------------
Carter, hand me my thinking grenades!

You can hang outside in the sun all day tossing a ball around, or you can sit at your computer and do something that matters. - Eric Cartman

Get the most out of Tek-Tips, read FAQ222-2244: How to get the best answers before posting.

http://lessthandot.com

 

[TheTeaMan]

Final Code when “your field” has Null value is:

IIf(IsNull(InStr(1,[your field],",")=True),0,1+Len([your field])-Len(Replace([your field],',','')))

Thanks Everyone!