Zip Codes in access 2000

[Guest]

I have a problem. I have takens a spread sheet from excel and imported it
into access. There are 380 records, all but 47 are in 5 digit form. The
others have the four digit extension. Everytime I import the spreadsheet, I
get an error message that says the records with the four digit extension do
not import. I tried to reformat the zip code column with the code
00000\-9999;0;_ which I got from the microsoft help section. After I did
this, I still have the zip codes with the four digit extension coming back
with an error. Does anybody know how to avoid this problem.

 

[MacDermott]

If your zip code field is defined as numeric, you can't store a dash in
it.
Make sure it's a text field.

HTH
- Turtle

 

[Bob Richardson]

When Access imports a file from Excel, it determines the type of data from
the first 1 or 2 records (not counting the heading line) of the Excel file.
I don't believe it even looks at the data type that you've set up for that
Access field (in Design view). In your case, Access sees that zip code is
OBVIOUSLY a numeric field, because the first records show that to be the
case, so it hiccups when it comes across a text field (e.g. 12345-1224).

The easiest solution, as dorky as this seems, is to CREATE a dummy first
record in your Excel file which will have text in all fields that might have
text. Once you've imported this Excel file, just delete that stupid first
record. Another solution might be to tack on an "-XXXX" to the zip code of
the first record. (e.g. change zipcode 12345 to 12345-xxxx) Then edit the
first record back to the correct zip code once you've imported it into
Access.