Exporting Leading Zeroes to .txt file

[Guest]

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", _
False
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
field.

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.

--
Regards

Jeff Boyce
Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

Microsoft Registered Partner
https://partner.microsoft.com/

 

[Guest]

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.