Zip Code import from Excel

Why would you want to prevent entry of any letters? A "digit-only" postal
code is very USA-centric. What about UK postal codes?

The main reason, in my mind, for not using a number data type is that
zip/postal codes ARE NOT NUMBERS! You don't add/subtract/multiply/divide
them ... OK, so you can, but you get meaningless answers. If they aren't
numbers, then they are characters ... text.

Just one person's opinion

Jeff Boyce
Office/Access MVP

"mnature" wrote in message
...
I, in turn, must disagree with John. It isn't that his method wouldn't
work,
but that there are sometimes several ways of getting to a similar place.

If you want a "long" zip code, you can simply put 00000-0000 as the
format.
This will prevent you from putting any letters, as you should only be able
to
put numbers in. A text field will not prevent you from putting letters
in.
If you put this in as the format, you do not need to type the hyphen.
Just
put the numbers in, and the hyphen will place itself at the point you have
indicated.

Knowing two, or more, ways of getting to a solution can be very handy.
Just
use whichever one fits best to what you are doing.

"John Vinson" wrote:

On Mon, 12 Dec 2005 14:36:06 -0800, "jjsun"
wrote:

I think we are close. I have created a table with the Field names ready
and
no records in the blank Access table. I formated the "zip" field as
text. I
then import (file, external database etc) the excel sheet but it gives
me an
error and says it cannot import file.

The Format of the field IS IRRELEVANT. It only controls how the field
is displayed, not what's stored. I must disagree with mrnature's
proposed solution - for one thing it will prohibit ever entering Zip+4
codes such as 83660-6354.

Instead use the Text *datatype*.

To populate the table, use File... Get External Data... Link to *link*
to the spreadsheet (rather than importing it), and create an Append
query to append the data into the pre-built Access table. If this is
giving you an error, please post the error number and description,
along with an example of the type of data being imported.

John W. Vinson[MVP]

Oops! Good point, Jeff. I am in the USA, and do get a bit USA-centric. I
will go sit in the corner, with my dunce cap on.

"Jeff Boyce" wrote:

Why would you want to prevent entry of any letters? A "digit-only" postal
code is very USA-centric. What about UK postal codes?

The main reason, in my mind, for not using a number data type is that
zip/postal codes ARE NOT NUMBERS! You don't add/subtract/multiply/divide
them ... OK, so you can, but you get meaningless answers. If they aren't
numbers, then they are characters ... text.

Just one person's opinion

Jeff Boyce
Office/Access MVP

"mnature" wrote in message
...
I, in turn, must disagree with John. It isn't that his method wouldn't
work,
but that there are sometimes several ways of getting to a similar place.

If you want a "long" zip code, you can simply put 00000-0000 as the
format.
This will prevent you from putting any letters, as you should only be able
to
put numbers in. A text field will not prevent you from putting letters
in.
If you put this in as the format, you do not need to type the hyphen.
Just
put the numbers in, and the hyphen will place itself at the point you have
indicated.

Knowing two, or more, ways of getting to a solution can be very handy.
Just
use whichever one fits best to what you are doing.

"John Vinson" wrote:

On Mon, 12 Dec 2005 14:36:06 -0800, "jjsun"
wrote:

I think we are close. I have created a table with the Field names ready
and
no records in the blank Access table. I formated the "zip" field as
text. I
then import (file, external database etc) the excel sheet but it gives
me an
error and says it cannot import file.

The Format of the field IS IRRELEVANT. It only controls how the field
is displayed, not what's stored. I must disagree with mrnature's
proposed solution - for one thing it will prohibit ever entering Zip+4
codes such as 83660-6354.

Instead use the Text *datatype*.

To populate the table, use File... Get External Data... Link to *link*
to the spreadsheet (rather than importing it), and create an Append
query to append the data into the pre-built Access table. If this is
giving you an error, please post the error number and description,
along with an example of the type of data being imported.

John W. Vinson[MVP]

No need ... I usually end up over-engineering (you should see the deck!).

Jeff

"mnature" wrote in message
...
Oops! Good point, Jeff. I am in the USA, and do get a bit USA-centric.
I
will go sit in the corner, with my dunce cap on.

"Jeff Boyce" wrote:

Why would you want to prevent entry of any letters? A "digit-only"
postal
code is very USA-centric. What about UK postal codes?

The main reason, in my mind, for not using a number data type is that
zip/postal codes ARE NOT NUMBERS! You don't add/subtract/multiply/divide
them ... OK, so you can, but you get meaningless answers. If they aren't
numbers, then they are characters ... text.

Just one person's opinion

Jeff Boyce
Office/Access MVP

"mnature" wrote in message
...
I, in turn, must disagree with John. It isn't that his method wouldn't
work,
but that there are sometimes several ways of getting to a similar
place.

If you want a "long" zip code, you can simply put 00000-0000 as the
format.
This will prevent you from putting any letters, as you should only be
able
to
put numbers in. A text field will not prevent you from putting letters
in.
If you put this in as the format, you do not need to type the hyphen.
Just
put the numbers in, and the hyphen will place itself at the point you
have
indicated.

Knowing two, or more, ways of getting to a solution can be very handy.
Just
use whichever one fits best to what you are doing.

"John Vinson" wrote:

On Mon, 12 Dec 2005 14:36:06 -0800, "jjsun"
wrote:

I think we are close. I have created a table with the Field names
ready
and
no records in the blank Access table. I formated the "zip" field as
text. I
then import (file, external database etc) the excel sheet but it
gives
me an
error and says it cannot import file.

The Format of the field IS IRRELEVANT. It only controls how the field
is displayed, not what's stored. I must disagree with mrnature's
proposed solution - for one thing it will prohibit ever entering Zip+4
codes such as 83660-6354.

Instead use the Text *datatype*.

To populate the table, use File... Get External Data... Link to *link*
to the spreadsheet (rather than importing it), and create an Append
query to append the data into the pre-built Access table. If this is
giving you an error, please post the error number and description,
along with an example of the type of data being imported.

John W. Vinson[MVP]