How to search for inappropriate characters?

[_Adrian]

I've got a table where one field is a phone number field.. the data is being
ported in as text (don't ask me why) even though the phone number is being
delivered to the DB simply as a string of 10 numerals (eg. 2065551212)

I need to query this field to look for 'bad data' .. basically anything that
isn't a number specifically... what would such a query look like?

 

[Brian Camire]

You might use criteria on your phone number field like this:

Like "*[!0-9]*"

in query to return the records where the phone number field contains
characters that are not digits.

If you wanted to always enforce the constraint that the phone number field
must contain only digits, you might set the Validiation Rule property of the
phone number field in your table to an expression like:

Not Like "*[!0-9]*"

 

[John Spencer (MVP)]

Regarding your comment "...the data is being ported in as text (don't ask me why)..."

Rule of thumb - if you aren't going to do arithmetic on it, then it is not data
that is numeric.

You can run into problems with storing phone numbers and other text data that
consists of only numeric characters. For instance, the largest long integer in
Access is 2147483647 which means that a phone number with an area code of 410
could not be stored as a LONG integer. It could of course be stored as a double.

Not relevant in a phone number, but in some other "numbers" such as medicaid
numbers is the leading zero(es) factor. If you store this data as a numeric data
type, then you always have to make sure that you format it with the leading
zeroes and you also have to make sure the user didn't accidentally drop a digit
during data entry. You can't tell if they entered ten digits whether that means
the number was supposed to start with a zero or they just missed one of the
eleven digits. If you make it text, you can always test the length for a
specific number of characters.