[KNRover]

Have an Excel spreadsheet containing names and addresses that I have linked to an Access database. Most zip codes are 5-digit, but a few are 9-digit with a "-" between the first 5 digits and the last 4. I have tried formatting the zip code column in the spreadsheet as Zip code + 4, Zip code, and Text. Zip code + 4 causes the zips that are only 5-digit to be preceded by a bunch of 0s, and when I link the spreadsheet to an Access database, the 0s still show and will (I assume) import that way into the table where I want to append them. The Zip code (5-digit) and plain Text formats allow the 5-digit codes to display properly, but all of the 9-digit zips display only "#Num!" in the linked spreadsheet.

I have tried googling for a way to fix this, but didn't find anything directly applicable. I would prefer not to have to go thru the process of separating the last 4 digits of the 9-digit zips into a separate spreadsheet column, add a field to the table to collect them, then after appending the records, write an update query that brings the codes back together (ugh!). Anybody know how to make 9-digit zips in a spreadsheet import properly into an Access table?

BTW, the format of the zip code field in the database is Text.

 

[Dragoen]

Hi KNRover,

Well the good thing is your Access zip field is text, otherwise you lose the leading zero on New England zipcodes. Normally you store the Plus4 digits in their own field and concatenate on the fly when you need to display/print the full zip code. Never store the hyphen, just use the format command in access to configure the display or concatenate it into the final string. Since you say the 9 digit zips in the spreadsheet with the field formatted as text come accross as #Num! then you can split the last four off into their own text field and the strip off the hyphens to clean up the zips. Then pass them to Access as two separate fields. Not what you wanted to hear but definitely more reliable.

hope that helps,

 

[KNRover]

Hi KNRover,

Well the good thing is your Access zip field is text, otherwise you lose the leading zero on New England zipcodes. Normally you store the Plus4 digits in their own field and concatenate on the fly when you need to display/print the full zip code. Never store the hyphen, just use the format command in access to configure the display or concatenate it into the final string. Since you say the 9 digit zips in the spreadsheet with the field formatted as text come accross as #Num! then you can split the last four off into their own text field and the strip off the hyphens to clean up the zips. Then pass them to Access as two separate fields. Not what you wanted to hear but definitely more reliable.

hope that helps,

It's what I thought about doing, but hoped there was a less involved way. Turns out there sort-of is. Apparently when you LINK, rather than IMPORT, spreadsheets in the tables section of Access, fields don't come over correctly. So after posting this message, I tried importing the spreadsheet, and lo and behold, the zip column came over as TEXT! The strange thing is that the phone numbers, which had also been set as text in the spreadsheet, still came over as Numbers. Despite that, when I opened the imported table, both the zip field AND the phone numbers came over just fine (no "#Num!" errors) and the phone numbers displayed (get this!) LEFT (not RIGHT) justified; i.e., as text.

So here's the lesson from this experience: If you have a spreadsheet containing 9-digit zip codes and want to append its data to a table, DO NOT LINK the spreadsheet; IMPORT it.