Transfertext Export number of decimal places

[Jameso1]

When I export to a fixed width ASCII format using transfertext my numeric
fields (including Long Integer!) come out to 2 decimal places. I have tried
to specify the number of decimal places in the query that I am exporting
but this has no effect. How do I make my Long Integer fields have no
decimal places and my double fields have 6 decimal places?

 

[Atomic Shrimp]

The thing to understand here is that although you specify the number of decimal places in the format of a double field, Access is actually storing the whole number in floating point form and only rounding for the display.

I use this method as a workaround:

for example, to round x to 3 decimal places:

((int((x*1000)+.5))/1000)

so to round to 2 decimal places:

((int((x*100)+.5))/100)

Taking this in stages (with x = 1.45678):

x=1.45678: *100
=145.678 : +.5 (because INT always rounds DOWN by merely chopping off the decimal portion)
=146.178 : convert to INTeger
=146 : /100
=1.46

I should say, that actually this rounds to x 'significant digits' because trailing zeroes are truncated. So you might want to convert to a string and pad out with trailing zeroes before exporting.

Mike

 

[Pat Hartman]

I've found the best way to do this is to export from a query rather than the table so I can use the format() function. Also, when I export fixed width files for mainframe use, they want the field zero filled.

Select Format(YourAmt,"0000000.000000") As FormattedAmt
From YourTable;