Counting Values in Single Field

[cfmiles]

Do you know if there is a way to count the number of times a certain letter/number shows up in a single field?

For example:
Field Value: 123ABC234ABC345ABC456DEF
If I want to count the number of times a "3" is in the field - can I do this?

So -
I could update another field to Y/N based on the number of times "3" shows up... If Field has 2 or more "3"'s - then Y, else N.

Obscure - can't find anything on this anywhere!

Any thoughts/ideas are appreciated!

 

[pcs]

do you need to look for just the count of a couple of specific characters, or do you need a count of the occurence (distribution) of every possible character in the field...

al




[This message has been edited by pcs (edited 07-23-2001).]

 

[Jack Cowley]

This function will count the number of "3's" in a string.

Public Function CountChar(MyString As String) As Integer

Dim n, i As Integer
Dim MyChar As Variant

n = 0
For i = 1 To Len(MyString)
MyChar = Mid(MyString, i, 1)
Select Case MyChar
Case "3"
n = n + 1
End Select
Next i

CountChar = n

End Function

To call this code use: CountChar([StringYouWantToCheck]) and it will return the number of characters you are searching for.

If the character is not always 3 then you can modify this code and pass the character you want to search for as well.

I will let you figure out the code to decided if the Yes/No field is true or not....

[This message has been edited by Jack Cowley (edited 07-23-2001).]

 

[cfmiles]

That is awesome Jack C - thanks for your help! That is just what I needed.