Multiple Currencies: Strawman

[PeteCresswell]

Somewhere over the horizon is lurking the need to maintain security
holdings in currencies other than US dollars.

We've already got a "tlkpTradingAccount" for each account and the
users are happy with being limited to a single denomination per
account.

What I'm thinking:
------------------------------------------------------------------------------------------------
- tlkpTradingAccount:
Add a new column: "CurrencyID", that points to a new table
"tlkpCurrency"

- tlkpCurrency
New table with three columns:
.NameShort: The ISO abbreviation for the currency. e.g. USD
.NameLong: Free-form text like "Dollars", "Pounds Sterling",
"Euros"
.Format: MS Access type .Format descriptor. e.g. "$,###,###.
00"

- On each form and each report:
Identify any currency fields by adding "Currency" to .Tag

- Write a routine that, when fed a pointer to a form, boogies through
all the fields on that form and alters .Format for those with
"Currency" in their .Tag - using the format in tlkpCurrency

- Write another routine that does the same thing for a report.

- Assume that all currencies will be decimal. i.e. they follow the
same rules for basic arithmetic operations.

 

[PeteCresswell]

- Assume that all currencies will be decimal. i.e. they follow the
same rules for basic arithmetic operations.

- Hope and pray that the users will not require reports whose totals
span trading accounts of different currencies.

 

[Dirk Goldgar]

Somewhere over the horizon is lurking the need to maintain security
holdings in currencies other than US dollars.

We've already got a "tlkpTradingAccount" for each account and the
users are happy with being limited to a single denomination per
account.

What I'm thinking:
------------------------------------------------------------------------------------------------
- tlkpTradingAccount:
Add a new column: "CurrencyID", that points to a new table
"tlkpCurrency"

- tlkpCurrency
New table with three columns:
.NameShort: The ISO abbreviation for the currency. e.g. USD
.NameLong: Free-form text like "Dollars", "Pounds Sterling",
"Euros"
.Format: MS Access type .Format descriptor. e.g. "$,###,###.
00"

- On each form and each report:
Identify any currency fields by adding "Currency" to .Tag

- Write a routine that, when fed a pointer to a form, boogies through
all the fields on that form and alters .Format for those with
"Currency" in their .Tag - using the format in tlkpCurrency

- Write another routine that does the same thing for a report.

- Assume that all currencies will be decimal. i.e. they follow the
same rules for basic arithmetic operations.

------------------------------------------------------------------------------------------------

Anybody been here?

A better way?

I have done almost exactly what you describe, for an application that
manages international trade shows. If it would help, here are a couple of
functions I used:

'----- start of code -----
Public Function fncSetCurrencyFormats(frm As Access.Form, CurrencyFormat As
String)

' Scan the argument form and all subforms it may contain for any
controls
' with the word "CURRENCY" in their Tag properties. For each such
control
' that is found, set the control's Format property to the value passed
' in the CurrencyFormat argument.

' A variety of unimportant errors are possible in this process,
' which we'll want to ignore.
On Error Resume Next

Dim ctl As Access.Control
Dim sFormat As String

If Len(CurrencyFormat) > 0 Then
sFormat = CurrencyFormat
Else
sFormat = "Fixed"
End If

For Each ctl In frm.Controls
If InStr(ctl.Tag, "CURRENCY") > 0 Then
ctl.Format = sFormat
Else
If ctl.ControlType = acSubform Then
fncSetCurrencyFormats ctl.Form, sFormat
End If
End If
Next ctl

End Function

Public Function fncSetCurrencyFormatsRpt(rpt As Access.Report,
CurrencyFormat As String)

' Scan the argument report and all subreports it may contain for any
controls
' with the word "CURRENCY" in their Tag properties. For each such
control
' that is found, set the control's Format property to the value passed
' in the CurrencyFormat argument.

' A variety of unimportant errors are possible in this process,
' which we'll want to ignore.
On Error Resume Next

Dim ctl As Access.Control
Dim sFormat As String

If Len(CurrencyFormat) > 0 Then
sFormat = CurrencyFormat
Else
sFormat = "Fixed"
End If

For Each ctl In rpt.Controls
If InStr(ctl.Tag, "CURRENCY") > 0 Then
ctl.Format = sFormat
Else
If ctl.ControlType = acSubform Then
fncSetCurrencyFormats ctl.Report, sFormat
End If
End If
Next ctl

End Function
'----- end of code -----

Of course, I'm not saying there isn't a better way to handle this problem;
just that this is what I came up with.

 

[PeteCresswell]

Of course, I'm not saying there isn't a better way to handle this problem;
just that this is what I came up with.

Sounds to me like a textbook case of two great minds arriving at the
same conclusion given the same data..... -)

Thanks.

 

[david]

We don't modify the form properties in most places.
Instead, we just put the ISO code as a label:
100.00 USD
And use a generic numeric format for the number. We hardly
ever use currency symbols like $, and never in any place that has
multiple currencies, only in the equivalent home currency in some
places.

On a typical form, there might only be one or two places where
the currency is formatted using the format table, and that is coded
in rather than using a tag property.

Generally, we don't use the format field for reports, because
it would put multiple currencies in a column in different formats,
but when we group by currency, the fields which use the format
table are coded in, rather than using a tag property.

Face Value AUD USD $1000's
100,000 AUD 99500 79.6

50,000 USD 37000 37


Total: 116,600 USD

client 1
$99,500.00 AUD formatted as AUD, with currency symbol from table
client 2
$37,000.00 USD formatted as USD, with currency symbol from table
client 3
0.00 GBP most of the currency symbols are missing.

(david)