Query Calculation Formatting...+Export automation

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Along the lines of an earlier question..
I have a query that includes several fields that perform mathematical calculations. As makes sense, the results originally showed up to several decimal places (ie 12.35)
I am exporting my query to a text file. It is critical that the calculation data have no trailing decimal places ( ie 12, not 12.00). Rounding of the number would be fine
I thought I had it solved by changing the properties of the calculated field to a fixed number with 0 decimal places. The result looked good in the table. Unfortunately, when I export to the text file, I see the numbers continue to show up with two decimal places
Can anything be done in the query to round or truncate the calculation results

And..
The resultant text file must contain only the data (no headings, etc.). It must be tab delimited with no quote marks around any text data. It seems the Export command is the only solution. I see no way to perform the specified export in a macro. Correct me if I'm wrong. If so, are there any suggestions for how to implement the export command automatically
Thank you in advance. I know it's a longwinded question. Just my nature to be verbose
-Greg
 
Hi Greg,

You can use the CLng() function or other number
formatting functions to format your calculation results
the way you want them in the query so that they will
export properly.

You can export using a macro, but you will have to set
the criteria manually first(tab delimited, no text
qualifier, no headings, etc), and save the export
specification. To save the spec's when exporting
manually, click on the advanced button. If you click
that, you should get another dialog with a "Save As"
button to save your specs.

Then, in the future, to export using those criteria in a
macro, use the transfertext action.

HTH

-Ted Allen
-----Original Message-----
Along the lines of an earlier question...
I have a query that includes several fields that perform
Click to expand...
mathematical calculations. As makes sense, the results
originally showed up to several decimal places (ie 12.35).
I am exporting my query to a text file. It is critical
Click to expand...
that the calculation data have no trailing decimal places
( ie 12, not 12.00). Rounding of the number would be fine.
I thought I had it solved by changing the properties of
Click to expand...
the calculated field to a fixed number with 0 decimal
places. The result looked good in the table.
Unfortunately, when I export to the text file, I see the
numbers continue to show up with two decimal places.
Can anything be done in the query to round or truncate the calculation results?

And...
The resultant text file must contain only the data (no
Click to expand...
headings, etc.). It must be tab delimited with no quote
marks around any text data. It seems the Export command
is the only solution. I see no way to perform the
specified export in a macro. Correct me if I'm wrong. If
so, are there any suggestions for how to implement the
export command automatically?
 
Hi Greg,

Glad it worked for you. Yes the export spec's are saved
in the mdb file. They are stored in the MSysIMEXSpecs
and MSysIMEXColumns tables (system tables are hidden by
default - you can view them by going to Tools|Options and
electing to view system objects).

Good luck with the Transfertext macro.

-Ted Allen

-----Original Message-----
Ted,
Thank you for the help. The CLng function worked great to clean up the exported data.
I've been able to save the export specifications. I
Click to expand...
assume it is embedded within the .mdb file.
Currently, I'm trying to implement the Export
Click to expand...
specification with the transfertext command. I'll let you
know if I have any problems.
 
Back
Top