TransferText acExportDelim unexpectedly truncates numeric field

[Jay]

Hi All -

I'm using a VBA procedure to export data from an Access table to a CSV file.
The table has nine text fields and one single-precision, numeric field. The
single-precision field contains numbers that are displayed with a variety of
decimal places, for example 0, 0.1, 5, 7.215, etc.

The following VBA statements export the data properly to a CSV file with the
exception that the numeric field is truncated at 2 decimal places. For
example, the value 7.215 becomes 7.21 in the CSV output file.

outFName = "G:\Data\MyData.csv"
DoCmd.TransferText acExportDelim, , "tblMyData", outFName, True

Any ideas why this value is being truncated? I can't find any options for
setting decimal places for an export using the TransferText method.

Note that value zero (0) in a record shows up in the CSV field as 0.00, the
value 0.1 as 0.10, and the value 5 shows up as 5.00. Also, if I perform the
export manually in Access (File, Export... to run the 'Export Text Wizard'),
the same thing happens (shorter values are formatted to two decimal places
and longer values are truncated to two decimal places).

Thanks in advance,
Jay

 

[Paul Shapiro]

Just a guess, but open the table in design mode and see if there's a fixed
format property set to display 2 decimals for that field.

 

[Jay]

Thanks for the reply Paul -

Good thought, but the table's field is not formatted for two decimal places.
That's what is a bit surprising about this phenomenon; decimal places is set
to the default 'Auto' and the values are being truncated (not rounded for
display).

A check shows that the field size is 'Single' and the Decimal Places is
'Auto'. In fact, the table is dynamically created by feeding an SQL statement
to the DoCmd object, so no field properties are reset during or after
creation:

strSQL = "CREATE Table tblMyData (myTextField1 Text, myTextField2 Text," & _
"myTextField3 Text, myTextField4 Text, myTextField5 Text," & _
"myNumericField Single, myTextField7 Text, myTextField8 Text," & _
"myTextField9 Text, myTextField2 Text);"
DoCmd.RunSQL strSQL

I'll keep working to find the cause or a solution and will post if new
information or a solution becomes available.

Thanks again for the lead,
Jay

 

[Jeanette Cunningham]

Jay,
this always happens when saving number fields as text during export to a
text file.
One work around is to export a query instead of a table.
Add a calculated field to the query that converts the number to text
In a new column in the query in the Field row type:
Expr1: CStr([TheNumberField])
Under the column for TheNumberField, uncheck the Show CheckBox

Export the query instead of the table

Jeanette Cunningham

 

[Jay]

Hi Jeanette -

I was just running some tests on simple tables and discovered what you
describe. It seems that the process of text-exporting any number in a table
truncates the number to two decimal places regardless of the value of the
number (and the number of decimal places displayed...).

Thanks a bunch for this information. I'll stop pursuing the issue and go
with your work-around or an equivalent.

Jay

Jay,
this always happens when saving number fields as text during export to a
text file.
One work around is to export a query instead of a table.
Add a calculated field to the query that converts the number to text
In a new column in the query in the Field row type:
Expr1: CStr([TheNumberField])
Under the column for TheNumberField, uncheck the Show CheckBox

Export the query instead of the table

Jeanette Cunningham

Thanks for the reply Paul -

Good thought, but the table's field is not formatted for two decimal
places.
That's what is a bit surprising about this phenomenon; decimal places is
set
to the default 'Auto' and the values are being truncated (not rounded for
display).

A check shows that the field size is 'Single' and the Decimal Places is
'Auto'. In fact, the table is dynamically created by feeding an SQL
statement
to the DoCmd object, so no field properties are reset during or after
creation:

strSQL = "CREATE Table tblMyData (myTextField1 Text, myTextField2 Text," &
_
"myTextField3 Text, myTextField4 Text, myTextField5 Text," & _
"myNumericField Single, myTextField7 Text, myTextField8 Text," & _
"myTextField9 Text, myTextField2 Text);"
DoCmd.RunSQL strSQL

I'll keep working to find the cause or a solution and will post if new
information or a solution becomes available.

Thanks again for the lead,
Jay