TransferText .csv

[test]

I have a macro which uses OutPutTo , to output results from a query to
excel, which works fine. In the same macro I needed to add to create a .csv
file for the same query.
I am using TransferText to do that, but unfortunately none of the field
formatting is brought into the the.csv file.
Any suggestions?
Any other better method to do this also welcome?
Urgent...
Thanks

 

[Ken Snell [MVP]]

Not sure which formatting you want, but use a query that is based on your
query, but replace the original fields with calculated fields that impose
the format you want by using the Format function. Then export this new query
to the text file.

 

[test]

When the format on each field is recognized by the OutputTo function, why
TransferText does not recognize it?

I did change the SQL Query itself, by putting the FORMAT function,that works
..
Also when I do
Round(Abs(Money),2) as Money
It gives me the error

"Circular reference caused by alias 'Money' in query definition's SELECT
list.

 

[test]

When I said format on each field, I meant in the Design view of the Query we
could set the format for each field. the query has the format set there but
while exporting to csv those formats and properties are not recognized. But
when using the OUTPUTTO the those format are kept in the excel.

When I use the FORMAT in the SELECT st it works, but thats not I was trying
to achieve.

 

[Ken Snell [MVP]]

OutputTo uses a different export process than TransferText... OutputTo
recognizes and keeps the formatting, TransferText doesn't. That is just how
it is.

Your circular error is caused by the fact that you're trying to use the
Alias name (Money) as the field name within the expression that yields the
calculated field's value. Use a different Alias name.
--

Ken Snell
<MS ACCESS MVP>

 

[test]

Is that the only way that I can create a .csv file from the macro?
Thanks.

 

[Ken Snell [MVP]]

Is *what* the only way? I'm not understanding your question here....

 

[test]

Is TransferText the only way to get data out from a query to a .csv file
from a macro.?

Thanks.

 

[Ken Snell [MVP]]

Macro, yes.

VBA code can be written that will open a recordset based on the query, open
a text file, and write individual values/records into the text file
directly.

 

[test]

when I use FormatNumber in the SQL query
I do not need commas in the number, need upto 3 decimal places.
How will I specify the FORMATNUMBER?

 

[Ken Snell [MVP]]

FormatNumber?

Try this:

MyCalculatedField: Format([MyOriginalField], "###0.000")

 

[Guest]

Hi I had the same problem, best solution is to use excel and then on the
output file location use .csv at the end and it creates a csv file, clunky
but it works

 

[Guest]

whoops, no it doesn't, it creates an excel file with a csv ending, some sort
of hybrid but if you try and re-import it into access you get rubbish, sorry,
back to the drawing board