Problems with different currencies

[perlfan]

Hi!

I would like to give users the opportunity to use my application with different currencies (e.g. Germans with €, Swiss with Fr./CHF).

I found out that it would be enough to format a field as 'currency' and then to simply change the currency in the regional settings of Windows. I did both, but it doesn't really work. I formatted the field as currency and changed the currency in the system control e.g. from Fr. to EUR - then the 'currency' formatting in Access 2007 always "gets lost" and is changed to strange formats like "Fr. "#.##0,00;"Fr.-"#.##0,00, so Fr. ist still the currency beeing displayed even though EURO is the current currency to be applied. So with that format it keeps using the Swiss currency instead of changing to EUR. I hope you understand my problem. I would like to enable users to simply switch in between currencies by simply changing the Windows settings.

Thanks a lot for help in advance! FRANK

 

[Simon_MT]

You have to set each currency and exchange rate to each transaction and use local (base) currency for management reporting. So a USD transaction would be converted at the ruling exchange rate to get to the local amount.

The problem with Currency formats is that it is misleading as the transaction may not be in the designated currency - you have to make each transaction relate to the relevant currency.

Simon

 

[perlfan]

You got me wrong - I don't want 2 currencies at the same time. I just want one single currency, but all 'currency' formatted fields should display the currency choosen in the regional settings correctly. And one should be able to switch in between currencies. Of course, one Euro would be one Swiss franc then. But even this doesn't work....

 

[Simon_MT]

No, you want to create a multi-currency solution. Then you have to tag each transaction with its designated currency and exchange rate.

You can't globally assign the Currency from the Regional Settings and expect the currency to remain. Essentially, all the Regional Settings is doing is prefixing the Currency Symbol. All that is going to happen is no matter what the currency is 'input' it will reflect the prevailing currency in the Regional Settings.

If you think about you what you are trying to create is one value but multi-value relative to the local currency. If you try to convert the Currency Value back to the local Currency it will only reflect the global currency in the Regional Settings, the other transactions in any other currency will be converted incorrectly.

Simon

 

[spikepl]

Bypass the problem - don't use Currency format on data of type currency.

 

[perlfan]

@Simon: I really don't to create a multi-currency-application. I am aware of the fact that currencies would not be converted, but it would rather be only the prefix that would be changed when switching the currency in the regional settings.

Bypass the problem - don't use Currency format on data of type currency.

--> are you saying, I should e.g. choose an ordinary number format and then attach the currency symbol before or after the number? I'd prefer a clean solution via the regional settings. It would also respect other prevailing formatting rules like the thousand separator. Has nobody successfully implemented the possibility to display different currencies based on the Windows regional settings?

Thanks for help - FRANK

 

[spikepl]

Don't mix too many things together. Currency symbol is one thing. Decimal separator another. We are here talking about the currency symbol. Allen Browne has this to say on the subject: http://allenbrowne.com/ser-44.html The article is old, but seems still to apply.

Update:

If all data and currencoy remain local, then there is no problem as such, since all numbers will be, implicitly, in the same currency. If data is exchanged between applications in different countries, or transactions in multiple currencies are entered, then you'll need to record currency for each number in a separate field anyway.

 

[perlfan]

Great article - he describes exactly the problem I am facing when Access is showing a "counter-intuitive behaviour" - as he calls it correctly - using the hard-coded value of the previous currency setting instead of the currency set in the regional settings. This was pretty much what I wanted to describe earlier, but he obviously does it in a better way I'll try the workaround later & will let u know of the outcome. THX - Frank

 

[spikepl]

One word of caution - if you write SQL in code, and in that SQL use decimal numbers, then wrap them in Str-function That will produce string out of numbers with "." as decimal separator, as required for SQL, irrespective of your locale settings. If you don't do this, the "," used as decimal separator in other countries will mess up your SQL.

 

[perlfan]

Thanks for the note on sql. I considered that issue already, so the numbers in my db get nicely stored and retrieved

BTW: The workaround with the field.format = currency works very well and is exactly what I needed. Thanks again!

One more thing - for a couple of user messages I would like to read the currency being used via VBA from the regional settings in order to integrate the curr. symbol into the string. Should I rather open another topic for that?

Thanks - FRANK

 

[spikepl]

Yeah - open another thread. Reading regional settings is a bit of a pain in Access.