Exclude values of a certain length

[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.

 

[Brian Camire]

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.

 

[John Vinson]

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.

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!