Trouble with count queryin Access

[Sethuel]

I'm trying to query a text string field, and count occurrences of specific values. For example, if the first value in the field were BRDRBTB, I would like a separate field to count the number of occurences of the letter B, which would be 3, and a field to count the number of occurences of the letter R, which would be 2. If the next value in the source field were RRSBRR, the queries would show 1 and 4, respectively. As with this example, these strings are not of uniform length.

Is there a way to do this? I'd be eternally grateful if someone could help. Thanks.

-Seth

 

[lespaul]

you are going to have to write some code to go through the string and count them, I don't believe there's any way to do this in a pure SQL format.

Leslie

 

[PHV]

If acXP or above:
SELECT (Len([field name]) - Len(Replace([field name], "B", ""))) As CountOfB,
(Len([field name]) - Len(Replace([field name], "R", ""))) As CountOfR
FROM your Table

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[lespaul]

I guess I misunderstood, I thought he wanted all the letters not just the B and R.

Leslie