Text Export Has Too Many Trailing Zeroes (e.g. 1.00000)

[kestefon]

Hi Everyone,



For some reason, whenever I export a query to text, every number has trailing zeroes after it. This happens with any number, even integers.

Examples:
1.000000000
5.250000000
0.600000000

Nine decimal places would be fine if the number extended that far, but I want to get rid of the trailing zeros so that 1.0000000 becomes 1, and 5.2500000 becomes 5.25, etc.

Now, I have noticed that a setting in the Control Panel > Region and Language > Formats Tab > Additional Settings > Numbers Tab controls the numbers of decimal places, but if I adjust it to two or zero it just cuts the data off at that point.

Any ideas?

[JoeM]

Hoe exactly are you exporting it? If you export it without formatting, it should drop all trailing zeroes (provided that the field in question is a Numeric datatype and not String/Text).

[kestefon]

I'm exporting it as a pipe-delimited text file. I don't mark the "export with formatting" box.

[JoeM]

Do you have an Export Specification you are using?
Are all these underlying number fields formatted as numeric data types?
In the query, is anything (or any function) being applied to these fields?

[kestefon]

No export specifcations that I know of. I'm just right-clicking the query, clicking Export > Text File, and then following the steps to make a pipe-delimited file. Also, the fields are indeed numeric, and there are no functions or calculations being applied to the fields.

[JoeM]

What program are you using to view the Text file?

[kestefon]

Notepad, although it looks the same in Notepad++. Are you guys saying that you don't have this type of issue when you export to text? I was starting to think it was like this for everyone using Access.

[JoeM]

So I did some testing, and I guess I never realized it did that. Formatting to two decimal places was always sufficient for me, I guess.

However, if you want to get rid of the trailing zeroes so that different entries may have a different number of decimal places, one way is to convert the number to a string in your query using the Cstr function, like this:
MyField2: Cstr([MyField])

[rpeare]

you might also be able to do it with

Myfield2: format([myfield], "##.##")

or something similar

[ItsMe]

The only way I was able to export the trailing zeros was to use formating at the table level. Perhaps formatting at a report level or your query will provide the results desired.

[kestefon]

Awesome, thanks... I'll try some of these options out.

[kestefon]

Using CStr in the query worked. Quick question regarding Access formatting--will Access ever round data during SQL query if you don't specify anything? For instance, if a record is 5.36543453, I'll want the query result to include as much of that as possible. That seems to be the case by default, but I just want to confirm.

[rpeare]

correct, when doing a mathematical function on a field it uses whatever is there, not just whatever's visible in the table view, so if you have 10 decimal places but the formatting only allows 2 decimal places it's still using decimal places 3 through 10.

[ItsMe]

I believe if you get a value from a formatted control it could possibly be truncated. If your query is getting the value from the table that is one thing but if your query references a control in a form that is another thing. I would definitely want to test the scenario if the source is anywhere other than the table.