Export (transfer Text) to a text file.

[Rob G]

I have a macro set up to transfer a table to a text file using a format spec.
I have set up a table and a query to fill the table with the data that I
want to export to a text file. All fields in the table are text fields. The
data in one field came from a currency field from a table and was converted
as follows:

Insert into MyExportTable (chkamount)
Select Format(nbrCheckAmount, "0.00")
From MySourceTable

When I export the table MyExportTable, the text field chkamount data gets
truncated as if it were a real numeric field. For example 3.14 and 3.10 and
3.00 are stored like: 3.14, 3.1, and 3 respectively.

My original MyExportTable was populated by a simple query without the format
statement and was a currency field or fixed field.

It seems no matter what I do to export the text values which should be
stored with zeros, the export or transfer text function tries to "help me" by
removing them.

Any ideas?

Rob (using Access 2003)

 

[Douglas J. Steele]

Create a query based on MyExportTable, and use the Format function in that
query.

Export the query, rather than the table.

 

[Rob G]

Thank you for the reply. It worked and I was able to delete the intermediate
tables as well. I never considered exporting from a query and fundamentally
I think there is a problem with exporting from a table.

For those who find this thread you need to redo your export specification
based on the export query rather than the export table otherwise you get a
silly error message: "The Microsoft Jet database engine could not find the
object, <yourexportfile>.txt . Make sure the object exists and you spell its
name and path correctly."