ansaurus

Question

multiple currency formats in one column - Access 2007

Answer 1

+2 A:

I suggest you include a currency type column, that is, one amount column, one column for the currency that the amount is in. You may also wish to include a column for the value in your own currency for accounting purposes, that is, the amount credited by your bank.

Remou 2010-05-19 20:41:48

+1, beat me to it :)

Lerxst 2010-05-19 20:44:29

Thanks for the advice, would have liked to have been able to avoid the need for an extra column but this will work fine. Cheers

glinch 2010-05-21 10:15:15

There isn't any other way to solve the problem, because it's two independent pieces of data and has to be in two different columns.

David-W-Fenton 2010-05-21 19:50:06

Answer 2

A:

ideally, you even have a tbl_Currency table, with:

id_Currency, PK, text, 3 characters (*)
currencyName, text

Then you can include an id_Currency FK field in other tables, where requested...

(*) 3 characters are enough to uniquely identify a currency as defined in the corresponding ISO currency list

Philippe Grondier 2010-05-19 21:38:56

Is it really worth the overhead to have a surrogate key here? Why not save a join (which has to be an outer join in case you have Nulls in the currency field) and just store the 1-3 characters right in the record? You can even use it for defining a format on the fly. This is, in fact, how I do it, and have done so as recently as the last 6 months.

David-W-Fenton 2010-05-20 23:22:14

Answer 3

A:

Note that @Remou's suggestion to have a currency type field does not really take care of the issue of formatting the field. In a single-record form, you could use the OnCurrent event to set the currency format. But in a continuous or datasheet form, this won't work. You'd think this would be an ideal situation for the Conditional Formatting feature, but its crippled in only changing appearance (color, bold/italic, etc.) and can't change the format property.

So, you're really forced to either display the currency in a separate column, or you have to replace the currency value with an expression that formats the number according to the currency type. Unfortunately, that makes the field non-editable, so I'd likely got with the separate column if I needed an editable continuous/datasheet form.

However, note that this is one of the many reasons why I just don't make continuous/datasheet forms editable except in very limited circumstances. I tread them as expanded listboxes, for displaying data, rather than as edit controls. This means that replacing an editable field with an expression has no downside.

But I'm in a minority in that regard.

David-W-Fenton 2010-05-20 01:13:58

Surely a separate column for the amount is not normal? What if a third currency is needed?

Remou 2010-05-20 09:07:08

Er, what? Amount: 100, Currency: €. Seems to me that covers it, no? You can then use whatever currency you want.

David-W-Fenton 2010-05-20 23:21:04

Now I am confused, is that not what I said? One column for amount and one for currency type? What is the point you are making in this post, which appears to recommend two columns for amount, as suggested by the OP?

Remou 2010-05-21 08:22:18

Er, isn't my first sentence clear? Having two columns to store amount and currency type still leaves you with the problem of formatting the amounts for display/printing. And that's what my post is about, the details of how to make that work.

David-W-Fenton 2010-05-21 19:49:10

ansaurus

tags:

views:

answers:

multiple currency formats in one column - Access 2007

related questions