Managing zip code

[Bikeman17]

I have to ship items in the USA using UPS. This carrier has defined the country in several zones (2-8). They have a freight chart that indicates rates for each zone. Each zone holds different zip codes.

An example: Zip code starting with 004-005 = zone 8, 010-089 = 8, 474-479 = zone 7, etc.

When I enter a Zip code in the Zip field, I want to get the zone automatically. I don't want to waste my time searching for the right zone. Right now, I am able to extract the first 3 digits of the zip code with this calculation:

LeftWords (Zip Code,3)

I need to create a second field using Case(Zip code = "004"......). As you can see in the example, some zip codes are binded (004 and 005) and other hold several codes (474 to 479). I don't want to defined each code, it will be too long and there won't have enough room (the 255 character limit).

This is the place where I screwed it up.

Any help would be appreciated

Bikeman17

[andygaunt]

Hi, create a second database with zip code (the first three letters) and then the zone field.

Each three letter zip code and its zone is a seperate record.

Now, in your main database, create a field with your calc Left(ZipCode,3)

Create a relationship from the calc to the new database zip code.

Now create a new field called zone.

Set tthis new zone field to lookup the zone in the new database based on this relationship.HTH

[danjacoby]

Danger, Will Robinson:

Andy put the formula ["Left(ZipCode,3)] correctly -- I think you misstated with "LeftWords (Zip Code,3)"; the "LeftWords" command will find the three words on the left, not the three characters.