Need To Exclude Certain Zip Codes

[shep]

I have a query that selects certain Zip codes by first 3 digits
SELECT qryContactNameV1.ContactName, Left(Contacts.Zip,3) AS FilterZip,
Contacts.Address1, Contacts.Address2, ([City]) & ", " & ([State]) & " " &
([Zip]) AS RemainAddress, Contacts.Company, Contacts.Title, Contacts.HomeTel,
Contacts.WorkTel, Contacts.CellPhone, Contacts.Email1, Contacts.Email2,
Contacts.Email3
FROM Contacts INNER JOIN qryContactNameV1 ON Contacts.ContactID =
qryContactNameV1.ContactID
WHERE (((Left([Contacts].[Zip],3))="770" Or (Left([Contacts].[Zip],3))="773"
Or (Left([Contacts].[Zip],3))="775" Or (Left([Contacts].[Zip],3))="777" Or
(Left([Contacts].[Zip],3))="778" Or (Left([Contacts].[Zip],3))="789"));

I need to exclude certain 5-digit codes associated with these 3-digit codes.
e.g., within 770, I may want exclude 77028

How can I do this?

Thanks

 

[Steve Schapel]

Shep,

Two approaches. Depends a bit on how many of these specific codes you
need to exclude, I guess. One is to make a simple table with one field
that lists the zip codes to exclude, and then you add this table to the
query, and use this as the basis of the criteria. The other is to write
them directly into the criteria of the [Zip] field in the query, similar
to what you have done for the Left([zip,3) criteria. For example, your
query's Where clause might look something like this...
WHERE ((Left([Zip],3) In("770","773","775","777","778","789")) And
([Zip] Not In("77028","77029")))

 

[shep]

I had to exclude only 5 so I used method 2.

Many Thanks!

Shep,

Two approaches. Depends a bit on how many of these specific codes you
need to exclude, I guess. One is to make a simple table with one field
that lists the zip codes to exclude, and then you add this table to the
query, and use this as the basis of the criteria. The other is to write
them directly into the criteria of the [Zip] field in the query, similar
to what you have done for the Left([zip,3) criteria. For example, your
query's Where clause might look something like this...
WHERE ((Left([Zip],3) In("770","773","775","777","778","789")) And
([Zip] Not In("77028","77029")))

--
Steve Schapel, Microsoft Access MVP

I have a query that selects certain Zip codes by first 3 digits
SELECT qryContactNameV1.ContactName, Left(Contacts.Zip,3) AS FilterZip,
Contacts.Address1, Contacts.Address2, ([City]) & ", " & ([State]) & " " &
([Zip]) AS RemainAddress, Contacts.Company, Contacts.Title, Contacts.HomeTel,
Contacts.WorkTel, Contacts.CellPhone, Contacts.Email1, Contacts.Email2,
Contacts.Email3
FROM Contacts INNER JOIN qryContactNameV1 ON Contacts.ContactID =
qryContactNameV1.ContactID
WHERE (((Left([Contacts].[Zip],3))="770" Or (Left([Contacts].[Zip],3))="773"
Or (Left([Contacts].[Zip],3))="775" Or (Left([Contacts].[Zip],3))="777" Or
(Left([Contacts].[Zip],3))="778" Or (Left([Contacts].[Zip],3))="789"));

I need to exclude certain 5-digit codes associated with these 3-digit codes.
e.g., within 770, I may want exclude 77028

How can I do this?

Thanks