MS Access help - count nbr of records where number of alpha chars in field

Forums › IT Pro and developers › MS Access help - count nbr of records where number of alpha chars in field > 3

kaz1963

2 posts

Wannabe Geek

#60231 22-Apr-2010 14:21

Hi there

I need to count the number of records in a table where there are more than three alpha characters in a specific field.

So if there were two records :

Record 1 : Field=OT12/12BV
Record 2 : Field=OT12/12B

and I ran the query, the count would be 1 - as the first record has four alpha characters in the field and the second record has three alpha characters in the field.

I have searched for other questions like this but I guess it is an unusual one as I can't find anyone trying to do exactly this.

Note: the count is for characters A - Z - not special characters.

Any help would be appreciated.

rvangelder

352 posts

Ultimate Geek

#321777 22-Apr-2010 17:35

You're going to hate this:

select * from MyTable where
Len(MyField) - Len(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(MyField, "A", ""), "B", ""), "C", ""), "D", ""), "E", ""), "F", ""), "G", ""), "H", ""), "I", ""), "J", ""), "K", ""), "L", ""), "M", ""), "N", ""), "O", ""), "P", ""), "Q", ""), "R", ""), "S", ""), "T", ""), "U", ""), "V", ""), "W", ""), "X", ""), "Y", ""), "Z", "")) > 3

I hate it too. Oracle SQL has a translate function that makes this quite a lot shorter.

kaz1963

2 posts

Wannabe Geek

#321907 23-Apr-2010 07:41

lol - yeah that really is ugly huh? Will give it a go.

rvangelder

352 posts

Ultimate Geek

#321910 23-Apr-2010 07:46

another way is to cartesian join the table with a number table (single column table, where each row is a number in sequence, 1 to 1000), then use an ASC function on MID to return 1 if the ascii code is between 65 and [whatever code for Z] or 0 if it's not A-Z, then sum the result.
it would be quite a lot shorter, but not as easy to understand, from a maintenance standpoint.