Auto Zip Code Entry

  • Thread starter Thread starter Bob
  • Start date Start date
B

Bob

Is there an example one can forward that can show what the
properties should look like in microsoft access for the
town field, zip code field, and the state field. So that
when one choices the town the state and zip code will
appear in your form.
 
Bob

A recent post in this newsgroup (tablesdbdesign) was trying to achieve
something similar -- given a form with a choice of a city/town, have the zip
code auto-fill.

The short answer is NO! Not only does my rather small town have at least
six different zip codes (just the zip5 portion), I've noticed that the city
of Portland happens in Oregon and in Maine (just to mention two states).

Now, if you were entering or selecting a zip code, those are tied,
one-for-one, with a city and state. I believe there are several commercial
operations selling just such a zip directory. And you can visit the Post
Office website to get that info on a one-by-one basis.
 
Is there an example one can forward that can show what the
properties should look like in microsoft access for the
town field, zip code field, and the state field. So that
when one choices the town the state and zip code will
appear in your form.
Click to expand...

This is impossible.

There are towns named Springfield in at least forty states.

Which state and zipcode do you want inserted in your table when you
pick Springfield?

The town of Springfield, Illinois has 36 zipcodes. Which one do you
want inserted in your table when you select Springfield, Illinois?

The properties of the fields are easy: State should be a two-character
Text; City a 24-character Text; and Zip either a 5-character Text
field if you just want the five-digit zip, or a 9-character Text if
you will (at least sometimes) have Zip+4.

Note (reading your other messages) that there *IS NO SUCH THING* as a
"combo box field". Fields are Number, Text, Currency, Date/Time,
Yes/No, Memo... but a combo box is JUST A TOOL, a device to enter and
edit data into a table (preferably using a Form).
 
The properties of the fields are easy: State should be a two-character
Text; City a 24-character Text; and Zip either a 5-character Text
field if you just want the five-digit zip, or a 9-character Text if
you will (at least sometimes) have Zip+4.
Click to expand...

If you may need to handle international addresses as well, City should
be at least 30 characters (some names are even longer), State at least
3, and PostalCode at least 9.
 
Dear John1 and John2.. <g>

I remember when I started with Access, I was careful to create field lengths
as small as possible, so as not to waste disk space. However, at some point
I read (or was told by someone), that Access was more efficient than Dbase
or FoxBase, (which was where I was coming from), in how it manages storage
so that it doesn't particularly matter if you specify a field length that is
too long - it will only use what is necessary. Since then, I've just left
my text fields at the default of 50.

1. Am I wrong? (About this particular issue, I mean! <g>)
2. If I'm not wrong, are there good reasons to specify smaller field sizes?

Thanks!
Fred Boer
 
If field sizes are larger than they need be, and they are indexed, then
queries and recordsets can be slow to run.
 
1. Am I wrong? (About this particular issue, I mean! <g>)
Click to expand...

You're quite correct. Access doesn't store trailing blanks, so storing
"ID" in a 2-byte field or in a 255-byte field stores two bytes in the
table.
2. If I'm not wrong, are there good reasons to specify smaller field sizes?
Click to expand...

The differences are subtle but real; if you want to restrict (say)
US/Canadian state/province codes to two bytes, it's simplest just to
provide two bytes so the user can't type "IDA". And as Joan says,
indexes are more efficient; in addition, if you open a Recordset,
Access must reserve memory for the maximum size of each field.
 
Well is there a method of creating the table with fields
that contain the city, state, and zip. You would input all
of the city, state and zip codes, so when you are in the
form and use the city combo box, then choice the state
combo box. The zip code will auto appear. The reason for
this method is one would typically know the city and state
but not the zip code. I am unclear on how each field
should be setup in the form so far as properties.
 
One other related subtle problem:

Access allows a max of 2000 characters ( or 4000 Unicode compressed ) per
record. So you can have 20 fields set to 255 characters each, and its fine
until some user decides to fill all the available space in all the fields,
then they get an error - and you get a support call. If you limit the field
lengths to 'sensible' values, this is much less likely to happen, and of
course can be avoided altogther by limiting the total character count - at
least you have control.

--
Regards,

Adrian Jansen
J & K MicroSystems
Microcomputer solutions for industrial control
 
Back
Top