Jan 5 2011, 09:26 AM
I have data that is exported from a website to an .xls file. A zip code field is part of this data. The exported zip code data has the leading zeros deleted. I can get them added back by formatting the column to special. However, I then import the .xls data file into Access using the transferspreadsheet command in a macro. When the zip code data is imported into Access, it loses the zeros again. The field type in the Access table that the data is imported to is 'Text'. I did notice that after I formatted the zip code data in Excel to special, that the field would display with a leading zero, but the underlying value was still missing the leading zero, which I guess would explain that is the data that is imported......
So my question would be: is there a way to get the zip code data in the Excel file to actually be the full zip code and not just displayed that way, OR is there a way for Access to format that field to add back the leading zeros?
Thanks so much!
Jan 5 2011, 10:04 AM
Sounds as though the Zip Code field in Access is numeric. It should be Text.
Create a new Text field in the table, and run an Update query along the lines of
UPDATE MyTable SET TextZipCode = Right("00000" & [NumericZipCode], 5)
Jan 5 2011, 10:13 AM
Thanks for your reply.....it does seem that it would be numeric, but it is definitely text, specifically for the reason that the foreign zip codes include letters. So I don't think the command you have below would work either since all the zip codes are not necessarily 5 characters long....some have the +4, and the letters in the foreign...
I think that it makes some sense that the data comes into Access without the zeros because even though the column is formatted as zip codes in Excel, the underlying data does not include the zeros......
Jan 5 2011, 10:33 AM
Are you saying that there are postal codes from other countries and +4 zip codes in the table, or simply that there will be?
If there is a mix, presumably it's only the old 5 character zip codes that are incorrect. You can use the same query I suggested, and add an appropriate WHERE clause, presumably something along the lines of
SET ZipCode = Right("00000" & [ZipCode], 5)
WHERE Country = "US"
AND Len([ZipCode]) < 5
Jan 5 2011, 11:02 AM
Thanks, yes there is a mix and will continue to be.....I'll give your statement a try in a little bit.....thanks again!
This is a "lo-fi" version of our main content. To view the full version with more information, formatting and images, please click here