I have a very simple macro which uses TransferText to export a query (qry_MyQuery) to a Tab Delimited text file (myfile.txt). I have used the File > Export > Advanced method to create a Specification file.

The macro then uses RunApp to run a batch file I've written which uploads myfile.txt to an ftp server.

The problem I have is that a field in qryMyQuery called [Amount] has values (e.g. 7.99). I have tried formatting these values as Number/Double or even text, but whatever I do, TransferText adds a currency symbol to the [Amount] field in the resulting myfile.txt (£7.99). The service I am using won't accept the currency symbol for [Amount].

I found http://www.access-programmers.co.uk/forums/showthread.php?t=38870 and tried entering this in the query field...
Amount: CStr(Format([AmountSource],"########.00"))
...this has no effect on the display of the number in the resulting query, as no currency symbol is displayed there anyway. But the currency symbol still appears in the txt file.

If I format the number as text it shows left-justified in the query, but still is exported as £7.99.

I just need the number to be exported, with or without quotes, e.g.:
"text" 6.49 "text"
or
"text" "6.49" "text"

Any idea how to do this?

TIA,

does it have the currency symbol in the txt file?

is this an export spec, or an import spec

i found that the two are slightly different, and you do get problems using an import spec to export with, etc

---------
how have you defined the field in the export spec?

Hi Dave,

It does have the currency symbol in the txt file, that's the problem :(

It is an export spec.

I can't see how I could do anything with specific fields in the export spec. I see that they're all listed but nothing changes if individual fields are selected.

I'm trying to attach a screenshot to show you...

Hello All, I am also experiencing this problem - some minute differences would be that I am using VBA to make it happen and the source table I am exporting is created specifically to be exported with a SQL statement that has my numerical field typecast as a double. IE --> SELECT CDbl(sourceTable.sourceField) as OutputField...
I am stumped as to why this is happening so here is the snippet of code I am using to export the table

dim path as string
path = "[the path to the output file]"
DoCmd.TransferText acExportDelim, "MovexUpdateSpec", "temp", path, 0

I too am using an export spec but when setting that up there doesn't appear to be any way to interact with the currency data type. Once again if anyone can help with this I would be massively appreciative.

One more chunk of information, it adds the currency symbol if i export via wizard or through code. Although with the wizard the preview of the export data has no currency symbols in it.