Linked Excel table with zip code data type error

[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

 

[Jeff Boyce]

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

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Victoria612]

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.

 

[Jeff Boyce]

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

 

[Victoria612]

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

 

[Jeff Boyce]

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