Criteria to be used in a ZIP CODE querie

[mrz via AccessMonster.com]

Hello All,

I have a simple first name, last name, address, apt, city, state and zip code
table.
Should a transit strike occur in New York I need to be able to offer my
organization with a quick list of staff members that live in the same zip
code area so they can carpool.

I created a query with all the necessary fields and I put for the zip code
criteria [ ]
so it prompts me to enter the zip code that I want. Furthermore, I created a
simple report out of this query so it will list the results.

Problem.
1) I guess my criteria is too general and is not giving me an exact result
all the time. For example, if I want everyone who lives in zip code 11101, I
only get people where there zip code is only 11101 but people that have for
example 11101-1265 are not being surface. Why? Can I input the criteria that
will only ask for the first 5 digits? Would this be the solution?

Observation:
Unfortunately, my table is not quite normalized. The zip code field contains
single and composed zip code. Example: I have individuals with zip 11101 and
also individuals with 11101-1235
Would this be a problem? I can't fix it ... That is the way the data came
from mainframe.

Thanks in advance for your time and reply.

MRZ
Happy Holidays!

 

[Jeff Boyce]

If you are saying you want to find all rows where the left-most characters
in the zip code start with "11101", you can use the Like operator in your
query's criterion. Something like:

Like [Enter first 5 characters] & *

Regards

Jeff Boyce
<Office/Access MVP>

 

[mrz via AccessMonster.com]

Thank you very much Jeff. It worked.

How to change the window header that reads 'Enter Parameter Value" ?

If you are saying you want to find all rows where the left-most characters
in the zip code start with "11101", you can use the Like operator in your
query's criterion. Something like:

Like [Enter first 5 characters] & *

Regards

Jeff Boyce

Hello All,

[quoted text clipped - 34 lines]

MRZ
Happy Holidays!

 

[mrz via AccessMonster.com]

Hey Jeff,

I just noticed that I pulling duplicate records.
How do I pull unique records.

Please note: It is ok for my table to have duplicates since a english prof.
can teach and also be a arquitech.

I just need to be able to bring him once on my ZIP CODE results.
Can this be done?

Thanks again!

If you are saying you want to find all rows where the left-most characters
in the zip code start with "11101", you can use the Like operator in your
query's criterion. Something like:

Like [Enter first 5 characters] & *

Regards

Jeff Boyce

Hello All,

[quoted text clipped - 34 lines]

MRZ
Happy Holidays!

 

[John Spencer]

I'm not Jeff, but you may be able to do what you want by using SELECT
DISTINCT in your query.

If you are doing this in the query grid
--Select View: Properties from the Menu
--Click in the grey area
--In the Query Properties window, set Unique Values to Yes

This will return one row for each unique combination of values in the row.
You don't want to show the field that says your gal is an English Prof and
an "arquitech". If you do then the combination of the values in the row
will not be unique for that individual and you will get two rows returned.
Also, you may have duplicates if in one record of the person, the zip is
11101 and in another it is 11101-1234

In the SQL view
SELECT DISTINCT Fname, LName, Left(ZipCode,5) as ZIP5
FROM YourTableName
WHERE ZipCode Like [Enter first 5 characters] & "*"

Obviously, you want to substitute your table and field names.

Hey Jeff,

I just noticed that I pulling duplicate records.
How do I pull unique records.

Please note: It is ok for my table to have duplicates since a english
prof.
can teach and also be a arquitech.

I just need to be able to bring him once on my ZIP CODE results.
Can this be done?

Thanks again!

If you are saying you want to find all rows where the left-most characters
in the zip code start with "11101", you can use the Like operator in your
query's criterion. Something like:

Like [Enter first 5 characters] & *

Regards

Jeff Boyce

Hello All,

[quoted text clipped - 34 lines]

MRZ
Happy Holidays!