Exporting Leading Zeroes to .txt file



I've found some good things on the discussion board but I can't get exactly
what I want. I have a table that is being exported to a .txt file. One of
the fields in the .txt file must have leading zeroes to make it 10 digits
long. I have the field in the table as a Number field and have included the
leading zeroes, but when I export to the text file the leading zeroes are
dropped. Here is my code:

Function Table_to_Text()

DoCmd.TransferText acExportDelim, , _
"Text1", _
"C:\Text_File_1.txt", _
End Function

When I changed the field type to Text, it removed the leading zeroes,
probably because the original query that builds the Text1 table uses a number

Thanks for your help in advance.

Jeff Boyce

A number is something you can add and subtract. Leading zeros are
placeholders (if to the left of the decimal place) and don't change the
value (which you can still add and subtract).

If you need a string of characters, including leading zeros, consider using
a query. In the query, you can format the number to display with leading
zeros, then you can use the CStr() function to convert it to a string of
characters. Finally, export the query, not the table.


Jeff Boyce
Microsoft Office/Access MVP

Microsoft IT Academy Program Mentor

Microsoft Registered Partner


I took your advice to build another query with all the fields of the table.
I added a column next to the field and inserted the following expression into
the query:

FieldName String: Right("0000000000" & [FieldName],10)

I then exported the query. This worked very well

Thanks for your help.

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question