Converting Zip Codes

[D Collins]

Hello,
I have a program that export data to a csv file, then I
save it as an Excel file. The leading zip codes are
truncated. I have formatted them as zip codes and they
look fine. Here's the problem: When I import them into
Access (even though they look great in Excel), the leading
zeroes are truncated.

How do I go about getting the zip codes to import into
Access correctly?

I have tried (concatenate("00",a1), but I don't always
need zeroes, as in a zip code such as 34567. Then I tried
to determine the length and have it append the proper
amount of zeroes, but the len function seems to always
return true no matter what length I test for.

I hope this makes sense.

Thanks, D.

 

[Guest]

One of the ways you can do this, although I'm sure someone will have a better
suggestion, is to click the Advanced button on the Import dialog in ACCESS.
Select the field that the zip code is in and format it to text. There is
also a save button; what this does is saves the import specifications to a
file. You can use this specification file for importing via a macro. If you
need more specific information please feel free to ask,

HTH Marty

 

[D Collins]

Hi,

There is no Advanced tab in the import dialog box. In
fact, the data field type greys itself out to TEXT.

Would you happen to have any other suggestions.

Thanks, D.

 

[Guest]

I'm not sure if this is the "correct" way to do this, but it works for me...

In Excel, select the column, then go to Data - Text to Columns. Click the
Text button on the last page.

If that doesn't work -- In Excel, select your code column and do Find 0
Replace with '0 .... Hit OK. Then go back and say Find ' Replace with
nothing. This will change all of your leading zeros to '0 which will format
them as text. The ' will not print or show up in Access.