Export of text field trims leading zeros

[John]

Hi

I am using following statement to export from a query to a CSV file.

DoCmd.TransferText acExportDelim, , "My Query", "MyFile.csv", True

The underlying table that the query is using has a text field with numbers
with leading zeros. Problem is once CSV file is generated it takes out the
leading zeros. What is the problem and how can I keep the leading zeros for
the text file in exported CSV file?

Thanks

Regards

 

[Allen Browne]

You have a choice of exporting either:
a) numbers, or
b) text.

For numbers, leading zeros are meaningless. 7 has the same numeric value as
007, but don't tell Bond. ;-)

For text, the leading zeros are significant, i.e. "7" is a single-character
string, whereas "007" is a 3-character string that does not match.

If you are not clear what I'm talking about, open the Immediate Window in
Access (press Ctrl+G), and enter:
? 7 = 007
When you press Enter, it shows True.
Then try:
? "7" = "007"
This is a string comparison, and so it returns False.

If you want to export a string (not a number), you can do that by using the
Format() function in your query, e.g.:
Format([SomeNumberField], "00000")
Access will then export it as a string, 5 characters long, in quotes.