Zip code query with 5 and 9 digit zip codes

[andie36]

Hi,
I have a table called List and a table called Zips. I want to pull
customers from the List table, and assign them a region based on the
information in the zips table. I have a query set up which pulls the names
from List and the region from the Zips file. This works, but there is a
slight kink in it. Some of the zip codes in the List table are 5 digits,
some are 5 plus the additional-xxxx. The query only assigns the region code
to the 5 digit zip. It isn't assigning it to the 9 digit ones. Is there a
way to say something to the effect of "If the first five digits of
list.zipcode = zips.zipcode region = zips.region."

 

[Chaim]

If you never need the information in the +4 zip code, only the first 5
digits, and assuming that these are stored as text, you could use something
like:

select zips.region from zips inner join list on list.zipcode =
left(zips.zipcode, 5)
where list.<customer name?> = <whatever>;

Good Luck!

 

[andie36]

Hi,

I tried doing this, but I am still having trouble. I understand the concept
and such, but I can't get it to work. I modified the code to use my field
names, etc.

select HarveyZips.ARCHDBREGI from HarveyZips inner join ACTUAL LIST on
ACTUAL LIST.ZIP =
left(HarveyZips.ZIP, 5)
where ACTUAL LIST.ZIP = HarveyZips.ZIP;

I am getting this error:
Syntax error(missing operator) in query expression 'ACTUAL LIST.ZIP =

left(HarveyZips.ZIP, 5)'.

Any ideas?

 

[Chaim]

select HarveyZips.ARCHDBREGI from HarveyZips inner join [ACTUAL LIST] on
[ACTUAL LIST].ZIP =
left(HarveyZips.ZIP, 5)
where [ACTUAL LIST].ZIP = HarveyZips.ZIP;

Can't have embedded spaces in names without enclosing in square brackets.