Counting alpha-numerics in a text field

[indyaries]

Greetings,

I need to get an actual count of real alph-numeric characters in a TEXT field. The field I'm checking is called DOCNO, and holds a Document Number. The size of this field is 14, and it is a TEXT-type field.

1. Here is an example of a valid DOCNO with 14 actual alpha-numeric characters: GRE21841324030

2. Here is an example of an invalid DOCNO without 14 actual characters: ABO8835 0

In example 2, there are eight actual alpha-numeric characters in the field. In a query I created a field called Char, and placed the following in the grid: Char: Len([DOCNO])

This returns 14. I had hoped that it would only count the "real" alpha-numeric characters. It also seems to count spaces. When I count the actual "spaces" taken by the example in "2" above, the result is 14.

Is there some way to only count letters and numbers in a text field? I want to alert the user if there are less than 14 "real" alpha-numeric characters in this field. Then they can research and correct the problem.

I had thought of posting this in the QUERY forum, but thought that anything that worked in a form may work in a query. I would rather use a solution in the QUERY, rather than the FORM.

Thanks for any assistance.

Bob in Indy -- currently at work.

 

[Alexandre]

If the number and relative places of letter and numeric characters are know in advance (ie a format exists) the easiest way is to make an input mask. It will allow you to constraint the number of characters, the type of each character (number, letter, either one or another), if they are obrigatory or facultative etc. See access help on Input mask for more details.

 

[pcs]

indyaries,

well, a space is, in fact, an alpha character...

check this thread for m. gurman's AllowOnly function:

http://www.access-programmers.co.uk/forums/showthread.php?s=&threadid=27062&highlight=allowonly

you can use this function and then check for length.

that is, if i read your question correctly

hth,
al

 

[indyaries]

Alex and Al,

Thank you both for your replies. I apologize for not explaining the problem correctly. Here is what's happening.

A text file is imported into Access 97. One of the fields (DOCNO) must contain 14 valid alpha-numeric characters -- no spaces allowed!

BTW, the info in the DOCNO field is input at other locations, hence I have no control on how the info is input into the field.

Anyway, once the info is imported into Access, I would like to have a query that only counts letters and numbers in the DOCNO field...I would not want a count of spaces. The only valid characters in this field are letters and numbers.

I had thought I could place a new field in the query grid after the DOCNO field, and call it Char (for Characters). I would want this field to return a count of only the valid values (letters and numbers) in that record. I can then set the query to filter on only those records that have a Character Count of less than 14.

Not sure if this can be done. Would be nice, as there are upwards of 28K records in the text file being used, and this data is imported and processed several times a month.

Thanks again for all of your assistance. This is a fantastic forum.

Bob in Indy -- now at work <smile>.

 

[Howlsta]

What's wrong with the AllowOnly function that PCS suggests? I'm pretty sure you can get this to disallow spaces. If you don't trust it remember you can use Trim[$] to strip the string of leading and trailing spaces first. Then do Len(yourStringExp) to check if it is too long.

Rich