Leading 0's issue from HTML to Access

[strict9]

Hello,

I have a spreadsheet in HTML format that contains zip codes. The zip
codes have leading zeros. When I import this data into Access (using
DoCmd.TransferText in VBA), I lose the leading zeros. The data has to
be stored in a TEXT field due to relationship restrictions.

I can find a way to force the leading zeros to be visible by using the
format 00000 in a number field, but using @@@@@ in a text field does
not cause the leading 0's to appear.

Any suggestions? Thanks in advance.

 

[strict9]

A quick note I forgot to add ... if I convert the HTML to an XLS
document, I still have the same problem. I can get the leading 0's to
be visible when I view them in Excel, but when selecting the field I
don't see a leading 0 in the "fx" box.

 

[Klatuu]

try:
Cstr(format([zip],"00000"))

 

[strict9]

Sorry, not sure where that is supposed to go?

 

[Klatuu]

Ahh, yes. That is a problem. The best idea I can come up with is to
transfer the spreadsheet into an intermediate table, then create an append
query to format your fields as it adds the rows to your permanent table.