Importing zip

  • Thread starter Thread starter ddpenn
  • Start date Start date
D

ddpenn

I am importing several Excel worksheets into new access
2000 tables. The zip code field is not converting to a 5
digit in the Access table. All zips beginning with 0 are 4
digit with the 0 dropped. I have the Excell field property
set to zip code. How do I get the Access table to show the
first digit 0? Help is appreciated.
 
Since Excel is not a database, using "mixed" data types in a column causes a
problem when Access has to "guess" which one to use. Access examines the
first 15 rows (or so) and makes a guess. If you want to "force" Access to
guess a text data type then make the first entry in that column a text
value. The reason you get errors is that the first 15 rows are all numeric
and then the text data shows up after Access has already guessed the data
type to be Numeric.

The way to make a text value in your data of numbers is to include a
character!
e.g. 123A
 
The ZipCode format in Excel is actually a number mask - the cell(s) are
still numeric. If you try to change the format in your spreadsheet, you
will lose the mask.

The only way I have found to get around this is to insert a single row at
the top of your spreadsheet and type in a couple of letters, abc, or
something like that. This will force Access to read your zip code data as
text and keep leading zeros. After you import your spreadsheets, you can
delete the first row in the table which contains the alpha characters you
typed.
 
Back
Top