Zip Code Query

[Guest]

I want to do a query based on zip codes but want the query to only consider
the first 5 numbers of the zip code, so if a zip code is entered as
12345-6789, the query would only consider 12345. How can I do this? Any
help would be appreciated.

Kevin D

 

[Marshall Barton]

I want to do a query based on zip codes but want the query to only consider
the first 5 numbers of the zip code, so if a zip code is entered as
12345-6789, the query would only consider 12345. How can I do this? Any
help would be appreciated.

Use a calculated field

Zip5: Left(zipfield, 5)

Uncheck the Show box and set the criteria to the desired zip
code (e.g. 12345).

 

[John Spencer]

Most efficient would be to use LIKE criterion if you are just searching.

LIKE "12345*"

If you want to display only the first five, then use Marshall's suggestion.

If you need to search and display then use Marshall's suggestion to display
the data and add zipcode field a second time and use the search criteria
I've suggested. Of course, if this involves only a few thousand records
Marshall's method is going to be just as fast (to the human) as any other
method.

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