large amounts of criteria

[Guest]

Hi,
I have a field that displays zip codes. I want to be able to sort those zip
codes into the different counties that they apply to. I currently have a
field that will change specific zip codes into a single county name if I type
in the criteria for each zip code. For instance: the field name is just "Los
Angeles" and the criteria fields look like: IIF(*ZIP CODE* between "90637"
and "90671)
Note: I have a large formula to get the zip codes because I had to trim down
a field in numerous ways in order to get just the zip, that is why I just put
ZIP CODE in *s, it obviously doesn't actually look like that, it just has the
formula.
Anyway: I want to be able to have a single field that can hold criteria to
change the zip codes to "los angeles" and "san diego" and "orange county" at
the same time. Please tell me how I can do this.
Thanks

 

[pietlinden]

probably the best way is to download the zipcode data from the us post
office website and then just join the tables.
use a join.

 

[Guest]

Do you know where exactly on the post office website I can find that
information. I am having trouble.
Thanks

 

[Allen Browne]

There's a copy of the US post codes here:
http://allenbrowne.com/ser-65.html

 

[Guest]

Thanks very much, thats really helpful. I doubt you have it, but do you know
if theres anything similar that includes county names as well, or perhaps an
easy way to just convert the cities to county. Thanks.
P.S. I really only need it for CA if that makes anything easier

 

[Guest]

I have found a website that lists cities by county. So now I am back to a
previous problem. Is there a way to set up a query with rules such that I can
say, for instance: if the city is "Los Angeles" or "Beverly Hills" or
"Inglewood" or "Hollywood" or "Culver City", return "Los Angeles"; if the
city is "Carlsbad" or "La Jolla" or "San DIego" or "Del Mar", return "San
Diego" and so on with a large amount of similar data; and do this all in the
same field? Or is there an easier way. Thanks again,

 

[Allen Browne]

I don't have the counties. Perhaps someone else does.

 

[Allen Browne]

The most sensible way to define those rules would be to set up a table with
a couple of fields that match the names, e.g.:
Los Angeles Los Angeles
Beverly Hills Los Angeles
Inglewood Los Angeles
Hollywood Los Angeles
Culver City Los Angeles
Carlsbad San Diego
La Jolla San Diego
...

You can store the data as it should be, and very easily return the more
generic value from the table.