Leading zeros are missing with zip codes

[Appache]

Hi,
I am trying to import Excel sheet into Access but I encounter with a problem
in ZIP code field. Even through I set ZIP code column with ZIP format in
Excel, when I import it into Access I am missing leading zeros. I tried
another way and created table in Access and set ZIP field as Text format and
tried to import excel sheet but it didnt work as well. I read many articles
about ZIP code problems but none of them fit into my situation so I am asking
this to you..In order to solve this problem I created an update query but I
didnt know what I should write in the update field.

Please help me with this problem..

 

[Ofer Cohen]

Can happen because when imported into Access it import that field as Numeric
and then it remove the leading zero's, for that you can create specification
and specify that it a text field

Or, format that field after it imported with leading zero's

Format([FieldName],"000000")

Note, the field type has to be text to accept leading zero's

 

[Dale Fye]

Ofer,

When I try to import an Excel spreadsheet, it doesn't give me the option of
creating a specification. What am I doing wrong?

Appache:

In my experience, this is because Access "interprets" the data that is in an
Excel spreadsheet the way it feels is best, based on the first row of data.
I've had this problem with dates as well. The way I generally handle this is
to open the Excel file and insert an empty row above below the headers and
above the data. Then I copy the first row of the data, and physically enter
a text value in the zip code field ("aaaaa") should work. Now, when Access
reads this file, it will interpret the zip code field as text. If there is
an easier way to do this, I'd love to hear it.

Dale
--
Email address is not valid.
Please reply to newsgroup only.

Can happen because when imported into Access it import that field as Numeric
and then it remove the leading zero's, for that you can create specification
and specify that it a text field

Or, format that field after it imported with leading zero's

Format([FieldName],"000000")

Note, the field type has to be text to accept leading zero's
--
Good Luck
BS"D

Hi,
I am trying to import Excel sheet into Access but I encounter with a problem
in ZIP code field. Even through I set ZIP code column with ZIP format in
Excel, when I import it into Access I am missing leading zeros. I tried
another way and created table in Access and set ZIP field as Text format and
tried to import excel sheet but it didnt work as well. I read many articles
about ZIP code problems but none of them fit into my situation so I am asking
this to you..In order to solve this problem I created an update query but I
didnt know what I should write in the update field.

Please help me with this problem..

 

[John W. Vinson]

In my experience, this is because Access "interprets" the data that is in an
Excel spreadsheet the way it feels is best, based on the first row of data.
I've had this problem with dates as well. The way I generally handle this is
to open the Excel file and insert an empty row above below the headers and
above the data. Then I copy the first row of the data, and physically enter
a text value in the zip code field ("aaaaa") should work. Now, when Access
reads this file, it will interpret the zip code field as text. If there is
an easier way to do this, I'd love to hear it.

The analysis is correct: Excel does not have "strong data typing" and the
Format of a field is irrelevant to Access. It just looks for the actual string
of characters. Since the string of characters making up an American 5-digit
zip code is a number, Access treats it as a number.

I usually will create the desired Table, with appropriate datatypes and fields
sizes, as an empty table in Access; and either import into it or (more
commonly) link to Excel and run an Append query to populate it.

John W. Vinson [MVP]