Question Locked

Access Database Multiple Currencies

0 helpful
4 replies
Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.

I Am currently busy building a database using Microsoft Access 2007, one of the requirements are that each office in the database be able to use a different currency, so if the office is in the UK then the currency will be Pounds ect.

I have set up a table for all the different currencies, there symbols ect, and linked it to ease office.

But i just cant get Access to display princess according to the offices currency, using the correct currency symbol...

any help and suggestions will be much appreciated.

4 replies

by tony hopkinson

up from locale, ie regional settings from windows. You don't want to be hard coding it.
Even if you got decimal point, thousand separators, and current symbol right. There's date and time formats, collations ....

You could try force a locale switch when run up the application, but that's notoriously flakey, tend to get confused and drop into US settings as a default
I've seen windows api stuff just flat out ignore every setting in the machine and leave me an error like
"1.000.000,00 is not a number"
or 31/12/2009 is not a date.

Haven't done access for ages, You might end up having to re-install it, and recreate the database for each change of country.

I know if you create a SQL server databse in a UK locale, and then restore it to another, it still has a UK colation char-set etc.

You have to change that manually.

by franscois

I have been looking in to the problem that I have been having, and the main issue is that Access does just that, it reports the currency format according to the PC's location and settings. we have our head office in Australia and each month we have to send our other offices Statements to show them how much money they have to transfer to us, each statement for each office that we make in the head office should be in the offices local currency.

For example:
London Office = Statement in Pounds
Montana office = Statement in USD

I do relies that I will have to build exchange rates in to this database as well in order to get sales reports act reporting sales correctly.

Some back ground:

My statement report is based on a Query that gets the office info, Statement charges, bank account details and currency for each office. In order to be able to open or create a statement you have to enter a date range for the statement.

So here is what I have come up with so far to help solve this issue:

1. Change all fields that were currency fields to Number.
2. Added Exchange rates table to the database.
3. On the report add some VB code to check what office the statement is for and then apply a custom formatting to the currency fields on the report.

The only thing is that I am very rusty on my VB code...

by marwauk

Franscois,

I am dealing with the same problem. I work in Switzerland but many of our clients and suppliers send us invoices and purchase orders in their own currencies. So I have had to create a currency table to enable conversions to Euros for reporting. However, I cannot seem to work out how to do the query?

Were you able to work something out?

This discussion is closed to new replies.