Exclude values of a certain length

  • Thread starter Thread starter ted.bowsman
  • Start date Start date
T

ted.bowsman

I have a column that includes zip codes, but many times
the zip codes are entered incorrectly or a foreign zips.
I want to set up a query that looks in column zip and
finds all values that are not equal to 5 characters in
length. How do I do this? Thanks so much.
 
You might use a criteria like

Not Like "?????"

on your field.

Alternatively, you might use an expression like

Len([Your Table].[Your Field])

as a calculated field in a query, and then use a criteria of

<>5

on the calculated field.

In either case, you can add

Or Is Null

to the criteria to also return records where your field is Null.
 
I have a column that includes zip codes, but many times
the zip codes are entered incorrectly or a foreign zips.
I want to set up a query that looks in column zip and
finds all values that are not equal to 5 characters in
length. How do I do this? Thanks so much.
Click to expand...

Create a query based on the table, and put in a calculated field

Len([zip])

Put a criterion on this of

<> 5

Suggestion: if you want to prevent such zipcodes from being entered in
the first place use an Input Mask of

"00000"

This will force the user to enter five numeric digits.

Note that excluding Zip Plus4 codes, Canadian postcodes, etc. may have
some undesired consequences if you ever want to send mail to a person
with such an address!
 
Back
Top