Mixed currency app

Question

One of my clients wants to deploy an Access app,  now used in the US,  to their Canadian location. Basically, it tracks projects where a fixed dollar amount has been approved. Individual purchase orders are issued against that approved amount.

The Canadian operation may issue PO's in C$ or US$, thus the app will be mixed currency.

I am considering maintaining the currency data type in the existing app, then marking each appropriate record with one additional field that specifies US$ or C$. Thus, for example, each PO header record would have a field specifying that the PO currency is C$ or US$.

At the top level, I would add two fields: Currency and Exchange Rate. The top level would budget out the project in C$, and use the Exchange Rate for any PO's issued in US$ for comparison against budget.

For UI and reporting, I would format displayed currency fields to display the proper currency symbol.

Workable? Am I missing something here? Thanks.

Answer 1

The Canadian operation may issue PO's in C$ or US$, thus the app will be mixed currency.

I am considering maintaining the currency data type in the existing app, then marking each appropriate record with one additional field that specifies US$ or C$. Thus, for example, each PO header record would have a field specifying that the PO currency is C$ or US$.

Hi Dave,

In one of my multicurrency applications where exchange rates can fluctuate, I use a couple of fields to "describe" the different amount:

- Value in reference currency (US$)
- Value in local currency
- Local currency symbol
- Exchange factor

In fact it contains some redundancy, because the value in reference currency can be calculated from Value in local currency X Exchange factor, but for total budgetting it is not necessary to do all these same calculations over and over again.

In case of amounts in reference currency the other 3 fields can be omitted.

All the currency fields are "currency independant", that is I only store the value. Appropriate currency symbols are added reporting stage.

Imb.

Answer 2

Imb, Thanks. Glad to hear from someone who's wrestled this particular animal before.

Not sure whether they want exchange to float within one project. I was thinking they set it at time budget is set, then keep same exch rate for that particular project throughout. But will ask them for sure. Saving the symbol is also a good idea. And setting an unambiguous reference currency: another good idea.

Plan to use Currency data type. BTW, have found that formatting currency data type in table design takes two passes to get something like #,###.00 to take. Design table, set format to #,###.00, save, run query, see $x.xx, open table in design (see that it has reverted back to Currency format) set format to #,###.00 second time (this time it takes) , save, run query, see x.xx

---

Dave Thompson

Answer 3

Plan to use Currency data type. BTW, have found that formatting currency data type in table design takes two passes to get something like #,###.00 to take. Design table, set format to #,###.00, save, run query, see $x.xx, open table in design (see that it has reverted back to Currency format) set format to #,###.00 second time (this time it takes) , save, run query, see x.xx

Hi Dave,

In my applications the fields are automatically generated, on the basis of a simple field definition. In the case of Currency fields in a table they get some default format, including the currency character defined in local settings (in my case  "€"). But to be honest, I really don't care how it is formatted in the table, I am only interested in its value. And the users never see the tables!

In Forms I use my own formatting, depending on what is needed. But users can easily change to almost any format they want (available in a shared library database), including any currency symbol.

With (Access) Reports I have hardly experience. I generate directly my reports using rtf-code. Here again I can play with almost any format using the same routine(s) as used for Forms.

Imb.

Answer 4

Imb. Thanks again for the information and direction. 25 years writing Access apps and this is my first multi-currency requirement.

Regards.

---

Dave Thompson