Dealing with different currencies

[Nizar]

Hi everyone,

What is the best solution to deal with different currencies in an Access Application? After creating a table that includes currencies codes I want to use them in the form to present and calculate prices.
After choosing the currency from a combobox, the price field must be presented in numeric values with the chosen currency code.

Thanks in advance,

Best Regards

 

[David Eagar]

Create a Currency table
cid Currency Synbol ExRate
1 US Dollar $ 1
2 Pound ₤ 0.4815
3 Yen ¥ 92.5147

and then use the exrate field to calculate price based on lookup selection

 

[Nizar]

Thanks David,
I'm afraid that I didn't clarify my request. I want to use the currency symbols to change the Price numeric format.
For example; when I choose £ from the combobox the Price format will be 12,45 £ ... I hope you will understand my point.
Many thanks,
Nizar

 

[Banana]

Well, David's proposal would still work- if you just add one more column and perhaps call it curSymbol (I am not sure if symbol may be a reserved words so playing safe here).

In your forms, use a combobox to look up the symbol, so when you enter a new order record, it lists the price in numerical plus the symbol based on the combobox so you know what currency it used.

Did that help?

 

[David Eagar]

I guess it is how sophisticated do you need to make this?
For each sales order (line?), you would need to store home currency and selling currency and then calculations based on the current exchange rate can be made.
If you require history, however, you would then need to store the exchange rate at the time into the Sales Order table

The queries for the calculations etc I will leave to the experts

 

[Nizar]

Hello again,
Thanks for your help. I'm sorry but I'm still confused. I will try now to clarify more.
The application is about orders management. The company has suppliers from all over the world; so different currencies.
In the Order creation Form we have to include all products of the order with their quantities, Unit prices...
I want to deploy this in my form: Once I selected the Supplier so i selected also the currency, the Format of UnitPrice in the Order Products subform takes in charge this currency.
Example:
Supplier: UK (Supplier.Column(2) = £)
In the Order Products subform:
UnitPrice: 12,12 £

Hope it's more clear this Time

Best Regards,
Nizar

 

[David Eagar]

OK, then add the correct currency to the Supplier table. Once that supplier has been chosen on the form, the suppliers currency should drop into sales order and the query should calculate back from suppliers currency into your home currency and store if required

 

[Nizar]

Thanks David,
but I'm not interresting in exchanging rate, my problem is Format: how to display numerical values with chosen currency?
Regards,

 

[statsman]

The only problem I see with this is you will have to update the exchange rate every day.

 

[Simon_MT]

I think the best way to handle currencies to store these the local and foreign amounts as Single Numeric format and forget the Currency Format and also deploy a Currency Table. I prefer to separate the currency from the numeric values i.e. there is a column for the currency and a column for the amount so that the amount is always a numeric value to enable further numeric functions. I think you can use something along the lines of:

=[Currency Symbol] & " " & Format([Amount],"##,##0.00")

All your calculations need to include * [Currency Rate]. If it is STG then * 1.

On your Entry forms and the reports you need to display either the Order Currency or Symbol whichever you prefer.

In multi-currency situation the Currency format is inappropriate. You need to specify the currency for each transaction.

The only problem with formatting foriegn currencies is catering for the francophile countries were the thousand separator and the decimal point are reversed.

Simon

 

[neileg]

Thanks David,
but I'm not interresting in exchanging rate, my problem is Format: how to display numerical values with chosen currency?
Regards,

You can't use the currency datatype to display different currencies without a huge amount of work. It is much easier to hold the amount as a number and then add the currency symbol for display purposes. You can concatenate the symbol with the amount as Simon MT suggests, but that will convert the numeric amount to text. Alternatively, in a form, use one text box for the symbol and one for the amount.

 

[Nizar]

Brillant suggestions Simon,Thanks Very much!
Have a nice day,
Nizar

 

[Nizar]

Thanks Neil, I'am working on it right now. Have a nice day
Regards,
Nizar

 

[Simon_MT]

Nizar,

If this helps I only state the Currency once on the Total Amount. The Total amount is prefaced with the Currency. On the the Entry screen left hand side of the Footer Local Amounts in STG are displayed and on right hand side Foreign Amounts in whatever currency.

I would also include the Sales Tax Code in with your Currency if Sales Tax needs to be added to the transactions even if is Zero Rated. This does not always apply so if Sales Tax is used again this needs to be editable.

Simon

 

[Nizar]

Simon,
That's what I'm doing now, I display the currency next to the Total amount upon selected supplier and it works. Now for further analysis (procurement, purchasing analysis) I must use the same currency to compare different suppliers for example. So, I have to exchange rates. Adding exchange rates in a table then converting it's not so helpful because it just needs changing rates every day so my idea is to use a freeware in the application ( currency converter for example...) a button open the freeware and the user can exchange and update rates everyday automatically.
Thanks Simon,
Regards,

 

[Simon_MT]

With the Exchange rates, even if using a conversion device does not necessarily equate to the rate of exchange you would receive from the bank. With your method, you can hold up your method of calculating the rate of exchange. Is is necessary to be competitive on exchange rates? If there is a time delay between the Order and receiving the payment there is likely to be an exchange gain or loss anyway.

Simon