outputting a query answer set to TXT and getting 8 decimals

[Randy]

so I have a simple query. I output the query data set to a tab delimited TXT file. I need the Amt field to be general (i.e no ",") and 2 decimals.

the input file has 8 decimals. when I view the query results the Amt field appears as I would want. however when I do the extract the Amt field goes to scientific notation because of the 8 decimals.

It seems that the outputting balances take the "formatting" of the table, and not of the query.

If this explanation makes sense, then any help is appreciated.

 

[Randy]

forgot the query statement for this field
GL Account Balance (Local Currency): Sum(IIf(["account currency"]="USD",IIf(Mid([account],5,1) Between "2" And "3",["GL Reporting Balance (USD)"]*-1,["GL Reporting Balance (USD)"]),IIf(Mid([account],5,1) Between "2" And "3",["GL Account Balance (Local Currency)"]*-1,["GL Account Balance (Local Currency)"])))

 

[isladogs]

When you export using the wizard, set the specification for the Amt field exactly as you want.

OR ... if all else fails, change your query to a make table query, run that & export that instead

 

[Randy]

thanks I tried the wizard and there is nothing to solve this issue. I did not think about a make table option. I will play with that, thanks

 

[Randy]

ok, the make table option still brings the 8 decimals into the "new" table. so back to same issue, when I export the table it includes 8 decimals. nice suggestion, thanks

 

[CJ_London]

It seems that the outputting balances take the "formatting" of the table, and not of the query.

No, the output is the underlying data and does not take account of any formatting properties be it query or table. If your input is 8dp it will remain as 8dp until you do something to change it.

You can truncate the value by using the format or round function in your output query. e.g.

format(myfield,"#.00")
or round(myfield,2)

 

[Mark_]

Have you tried wrapping your sum in FORMAT()? That should allow you to generate exactly what you are looking for export.

 

[isladogs]

Go back to your query and change the format to Fixed and the decimal places to whatever you want using the round function. Then try both the steps I previously suggested again