Linked Excel table with zip code data type error

  • Thread starter Thread starter Victoria612
  • Start date Start date
V

Victoria612

Hi!

I have a table that is linked from Excel with a column for zip codes. The
data type for the column in Excel is a Number, under Special - zip code.
Though the majority are US codes, there are some Canadian codes (which are 7
characters including the space, and incorporates letters), as well European
codes. Obviosuly this is giving me a #Num in Access - can anyone help?

Thanks!

Victoria
 
Zip codes are, well, "codes", not numbers. Change the datatype in Excel to
"text".

Regards

Jeff Boyce
Microsoft Office/Access MVP
 
OK - the problem with that is when I have a zip code that begins with a "0",
for instance in Framingham, MA, the zip code is 01702, it only displays 4
characters in Excel. It then links up to Access the same way and transfers
over as 4-digits.
 
If Excel treats it as "text", the "0" is just another character.

I suspect you'll need to:
1. change the formatting in Excel so those are "text"
2. drop the link in Access to that Excel spreadsheet
3. re-link to that Excel spreadsheet

Does it work now?

Regards

Jeff Boyce
Microsoft Office/Access MVP
 
Thanks Jeff - I actually figured it out - when I changed it to text, it
converted the pre-existing zip codes that began with a "0" to a 4-digit zip,
i.e., 01702 became 1702. I simply changed all the existing zips that were now
4 digits back to 5 digits, and now that the column is formatted as text, when
I enter a new zip starting with "0" it will recognize the "0" as just another
character, (like you said!).

Thanks for all of your help anyway!

Victoria
 
Thanks for posting your solution.

Someone else will undoubtedly run into the same issue and what you learned
may help them.

Regards

Jeff Boyce
Microsoft Office/Access MVP
 
Back
Top