Checking against input mask

[Guest]

I have a field imported from an external database that is often entered
incorrectly. I would like to have a query to pull all data that doesn't meet
a certain criteria.

All data should be of the format ########0#XX (10 digits with a 0 in the 9th
position followed by two alpha characters). I would like to find all that
have too many or too few alphanumeric characters, don't have the 0 in the
right place & don't have the two characters in the right position on the end.

Any help you can provide would be appreciated!

 

[John Spencer]

I think what you want is the following.

Not Like "########0#??"

If the last two characters can only be letters (a to z) then

Not Like "########0#[A-z][A-z]"

IF you are not using Access with the native jet engine the wildcards may be
different. You might need to use

Not Like "[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]0[0-9][A-z][A-z]"

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[Guest]

You got it - thanks.
--
Thanks,

Jason

I think what you want is the following.

Not Like "########0#??"

If the last two characters can only be letters (a to z) then

Not Like "########0#[A-z][A-z]"

IF you are not using Access with the native jet engine the wildcards may be
different. You might need to use

Not Like "[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]0[0-9][A-z][A-z]"

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..