Validate alpha-numeric 1

[CptTom]

Is there an easy way to validate data to see if the first character is alpha-numeric? We are talking about serial numbers & I want to find the people who have put in bad serial numbers which are identified as the first character being other than alph-numeric, instead, they put symbols, (.-_#, etc). If I can figure that out, the next question will be to run it on multiple fields simultaneously, serial numbers, registration numbers, year of manufacture, etc.-

 

[JerryDennison]

Could you not use an input mask to prevent this from happening in the first place?

In the input mask property use 'a' to allow any alphanumeric character only without requiring input or 'A' if you wish to require input. Add the number of characters for the number of positions to fill. For example, if your string MUST be 10 characters long use:

AAAAAAAAAA

in the input mask property.

 

[CptTom]

No, we are pulling data from systems that were written by other companies who have proprietary rights and we can not rewrite their code. I am trying to find a quick way to find those (over 3,000) operators who are too lazy to put in correct data. This still will not totally prevent it, but it will force them to put it in the correct format.

 

[JerryDennison]

Ah, IC!

In that case, your best bet is to build a function to use in a query to "flag" each record that bogus character(s).

If you're only interested in testing the first character the following should do it. Since it's a function you can call it as many times and on as many fields as you'd like in your query:

Public Function FlagBogus(strInput As String) As Boolean

Select Case Asc(strInput)
Case 48 To 57
'Valid numeric characters
FlagBogus = False
Case 65 To 90
'Valid uppercase characters
FlagBogus = False
Case 97 To 122
'Valid lowercase characters
FlagBogus = False
Case Else
'All other characters are assumed invalid
FlagBogus = True
End Select

End Function