Remove currency symbol when exporting

[CW]

I store our invoice values as Currency fields in tblInvoices, with the pounds
sterling symbol.
I need to export the invoice details to our accounting system (Sage Line 50)
and have built a process for this using TransferText.
However the Sage csv import spec doesn't like currency symbols. It will
accept only bare numbers.
I couldn't see any way to manipulate this in the Export Spec wizard.
I don't really want to remove the Currency format in my Access table, so
what's the best way round this?
Many thanks
CW

 

[Douglas J. Steele]

Create a query that uses the Format function (not the Format property) on
the fields in question to format them without the currency symbol. Export
the query, not the table.

 

[CW]

Douglas -
Thanks, I formatted the fields in the query that runs from the table as
Fixed instead of Currency, and now the values in the query have no currency
symbol. Great, I thought.
However, when I run TransferText and produce the csv, the symbols are still
there!!
Any further ideas please?
Thanks a lot
CW

 

[Douglas J. Steele]

Did you use the Format function like I suggested, or did you strictly set
the Format property?

 

[CW]

Er, guilty of not reading your guidance properly, sorry.
Although thinking about it, I'm not familar with how to do that (setting
Format as a Function and not within Properties).
Could you very kindly point me in the right direction, please?
Thanks Douglas
CW

 

[CW]

Sorry to be impatient about this but I need to get it working for when
everyone returns to work on Weds!
How do I use the Format function to exclude the currency symbol from values
within a query that I am then exporting to csv?
The values currently have their Property/Format set to Fixed and they do not
show the pounds symbol in the query.
But when I export it to csv, they show up!!!
Many thanks
CW

 

[Douglas J. Steele]

SELECT Field1, Field2, Format(Field3, "General Number")
FROM MyTable

If doing this through the grid, once you've dragged Field3 into the grid,
simply type Format( in front of the field name, and , "General Number")
after it. You may want to use something other then General Number: for
example, it's not going to show decimal points if there's no decimal part to
the number.

 

[CW]

Thanks, Douglas - I used Fixed so as to retain 2 DPs.
Unfortunately however the pounds symbol STILL shows up in the csv. It's a
devil, it won't lie down and die. I suppose I should be proud of the Great
British Pound for its survival instinct but in this case I'm very fed up with
it. We're going to have to produce the csv by the manual Export process
rather than by using TransferText - that's the only way I can get the data to
go through without the symbol.
Anyway, I really appreciate all your help
Thanks
CW

 

[Douglas J. Steele]

Sorry if it's an insulting question, but you ARE exporting the query, not
the table, right?

 

[CW]

No problem with you checking that - I have done sillier things before - yes,
definitely exporting qryInvoiceTransfer, not tblInvoices.
Thanks
CW

 

[John W. Vinson]

No problem with you checking that - I have done sillier things before - yes,
definitely exporting qryInvoiceTransfer, not tblInvoices.

Open qryInvoiceTransfer in SQL view and post it here. There's something that
isn't getting through - Doug's suggestions should do what you want.

John W. Vinson [MVP]

 

[CW]

Good idea John - here it is:
Thanks
CW

SELECT InvoiceTypes.TranType, tblInvoices.DebtorAccountNo,
tblInvoiceLines.InvLine1NomCode, tblInvoices.DeptNo,
Format([tblInvoices.InvDate],"dd/mm/yyyy") AS [Date], tblInvoices.InvNo,
Inquiries.Customer1Surname, Format([InvTotalNetValue],"Fixed") AS Expr1,
tblInvoices.TaxCode, Format([InvTotalTaxValue],"Fixed") AS Expr2
FROM (((Inquiries INNER JOIN tblInvoices ON Inquiries.Ref = tblInvoices.Ref)
LEFT JOIN InvoiceTypes ON tblInvoices.InvType = InvoiceTypes.InvType) INNER
JOIN qryInvoices ON tblInvoices.InvNo = qryInvoices.InvNo) LEFT JOIN
tblInvoiceLines ON tblInvoices.InvNo = tblInvoiceLines.InvNo
ORDER BY tblInvoices.InvNo DESC;

 

[Albert D. Kallal]

I would suggest that you DO NOT use the built in constants such as "fixed",
or currency with the format command

(it will pull your current formatting from the control panels
"international" settings"

ALWAYS...ALWAYS....ALWAYS use your OWN defined formats.

eg:

Format([InvTotalTaxValue],"0.00") AS Expr2

 

[CW]

Thanks Albert - I tried that but the query will not save with the format set
to 0.00, it reverts to Fixed! I tried changing it in both the SQL view and
the grid view.
This is a toughie - any further thoughts?
Thanks
CW

 

[Albert D. Kallal]

Thanks Albert - I tried that but the query will not save with the format
set
to 0.00,
it reverts to Fixed!

Ha...it does!!...I never noticed that. I would thus try then something like:

wrap the text around a cstr then...

such as:

format([MyAmountField],cstr("0.00"))

If you use the built in ones, then you subject to your internal settings,
and you do NOT want to have change this for every pc this runs on...

so, try the above.....

You could also try: "standard", and see what that gives you..

eg:

format([MyAmountField],"standard")

However, I think it best for *force* the format the way YOU want, and NOT be
dependent on some internal setting....

 

[CW]

Albert -
Thanks, I tried both of those, and they did save OK in the query and when I
ran the query it showed clean values without any pounds symbols.
Also when I went into the export spec and looked at the sample data in the
windows, it showed no currency symbols there either.
However, when I run the macro using that spec, same as ever the symbols are
in the csv file!!!!

Taking another angle: I tried deleting the spec from the macro and that
looks kind of promising. The forcing of the formats works OK, the csv has
neither the currency symbols OR the time (which is the subject of my other
post here on which you have been assisting too).
Only problem is, it does have the " " Text Qualifier throughout, which the
target system absolutely will not accept.
If there's some way we can strip those (other than by using an export spec),
then we would have a solution!
Many thanks
CW

Thanks Albert - I tried that but the query will not save with the format
set
to 0.00,
it reverts to Fixed!

Ha...it does!!...I never noticed that. I would thus try then something like:

wrap the text around a cstr then...

such as:

format([MyAmountField],cstr("0.00"))

If you use the built in ones, then you subject to your internal settings,
and you do NOT want to have change this for every pc this runs on...

so, try the above.....

You could also try: "standard", and see what that gives you..

eg:

format([MyAmountField],"standard")

However, I think it best for *force* the format the way YOU want, and NOT be
dependent on some internal setting....

 

[Albert D. Kallal]

That is strange. I would try re-creating the spec from scatch...and see if
that works.

I tried the follwing code:

DoCmd.TransferText acExportDelim, "testSpec", "qex", "c:\output.txt"

And, I also did the same from a maro.....

My query was:

SELECT Format([Amount],CStr("0.00")) AS MyA, Table1.Amount
FROM Table1;

Note how in teh above I export amont two times (to compare the difference.


0.00,$0.00
45000.00,$45000.00
99999.00,$99999.00
199999.00,$199999.00
123.45,$123.45

I would try using the transferText command in code as opposed to a macro.

I would also try rebuilding the spec, and simply set the text qualifier to
none.
(it possible the spec saves, or retains information about the original
query).

It is possible that there is a "international" difference here. You also
don't mention what version of access..


--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
(e-mail address removed)

Albert -
Thanks, I tried both of those, and they did save OK in the query and when
I
ran the query it showed clean values without any pounds symbols.
Also when I went into the export spec and looked at the sample data in the
windows, it showed no currency symbols there either.
However, when I run the macro using that spec, same as ever the symbols
are
in the csv file!!!!

Taking another angle: I tried deleting the spec from the macro and that
looks kind of promising. The forcing of the formats works OK, the csv has
neither the currency symbols OR the time (which is the subject of my other
post here on which you have been assisting too).
Only problem is, it does have the " " Text Qualifier throughout, which the
target system absolutely will not accept.
If there's some way we can strip those (other than by using an export
spec),
then we would have a solution!
Many thanks
CW

Thanks Albert - I tried that but the query will not save with the
format
set
to 0.00,
it reverts to Fixed!

Ha...it does!!...I never noticed that. I would thus try then something
like:

wrap the text around a cstr then...

such as:

format([MyAmountField],cstr("0.00"))

If you use the built in ones, then you subject to your internal settings,
and you do NOT want to have change this for every pc this runs on...

so, try the above.....

You could also try: "standard", and see what that gives you..

eg:

format([MyAmountField],"standard")

However, I think it best for *force* the format the way YOU want, and NOT
be
dependent on some internal setting....

 

[Jeanette Cunningham]

Hi, just jumping in here with a test from a different international
country - Australia using Access 2003.
I was able to export the file with the format CW needs.
In my version of Access in setting up the export specification, I had the
option of choosing {space} from the dropdown for text qualifier.
I exported it from a button on a form using code same as Albert's using the
save spec that had {space} for the text qualifier.

Jeanette Cunningham

Thanks Albert - I tried that but the query will not save with the format
set
to 0.00,
it reverts to Fixed!

Ha...it does!!...I never noticed that. I would thus try then something
like:

wrap the text around a cstr then...

such as:

format([MyAmountField],cstr("0.00"))

If you use the built in ones, then you subject to your internal settings,
and you do NOT want to have change this for every pc this runs on...

so, try the above.....

You could also try: "standard", and see what that gives you..

eg:

format([MyAmountField],"standard")

However, I think it best for *force* the format the way YOU want, and NOT
be dependent on some internal setting....