Different currency formats 1

[TrollBro]

Please any suggestions?
I have a report that I need to display amounts in dollars, euros, and pounds, all on the same report, but I cannot seem to find a way to so. I can change the language settings to get dollars and euros, but not pounds, or select the UK to get pounds but not euros. Isn't there a simple way to format the fields on the report separately without changing the whole language preference? Calculating the conversions isn't a problem - just how to display as pounds versus dollars versus euros.

Thanks

 

[lameid]

Hmmm... the format function alows you to use text when formatting numbers. Check it out in help. If you had your currency symbol in your table where you identify currency, you could just join the table in and include the symbol in your query. Then Use that symbol field to format your currency. So your control source would look soemthing like:

Format(Currency_To_Format, Currency_symbol & "#,##0.00;" & "(" & Currency_symbol & "#,##0.00);")

This assumes of course the currency symbols go in the same place, I'll admit ignorance on that one. Alternatively you could just store an appropriate format string in your currency table and use it in your format function. As for getting the appropriate currency character, I'd recommend copy and paste from a good source.

 

[TrollBro]

LameID

I used this based on your suggestion and it seems to work:
=Format([os1],"£ " & "#,##0.00;")

But I wasn't sure what to do with, or how to make work, the 2nd half of the formula you provided so I might be missing something:

& "(" & Currency_symbol & "#,##0.00);")

The key point from your suggestion was separating the symbol from the standard format and that's what I was missing. Many Thanks!

 

[lameid]

The point is that the recourd source of the report should have a field named Currency_symbol added to it. The Two parts of the format seperated by a semicolon define the positive and secondly negative display. My example puts parenthesis around a negative number rather than using the negative sign. You can add up to too more possiblities of format types, 0 and null.


I am assuming you know the target currency and have a table defining the currencies. I am suggesting you add a column to the table that defines currency for the symbol...

Currency_Code Currency_symbol
USD $


Your currency_code may be different. Then I would join the currency table into your base query (or make one) and add the Currency_Symbol from the currency table. Then you don't have to change the control source, ever.

So in my orignal example, if your query had...

Currency_To_Format Currency_symbol
5.25 $
-2 $
10 £
-12 £

Your report would show...
$5.25
($2.00)
£10.00
(£12.00)

Adjust the field names as you see fit. I also suggested instead of just the currency symbol, you may want to format them different with say the currency position in a different place. More likely, you may want a different precision on a different currency.

 

[lameid]

This happens to be relevant to something I am working on so I have done a little more poking. There are International Currency_Code standards (three letters like USD for US Dollars) and some currencies do vary the currency location, so by rights you should have a format string for each currency. The real problem is that most fonts do not include all currencies. I have seen reference to a shareware font, Code2000 which allegedly contains all the currency symbols. I suppose alternately, you could also store the name of a supporting font and on the ONFormat event of the section change the font of the control. Wikipedia also references a generic currency symbol where it is unknown (depicted on wikipedia).

 

[lameid]

Based on

http://www.jhall.demon.co.uk/currency/

It would seem embedding the currency code with the currency is the best way to go.