dynamic currency formatting in a report

[jaworski_m]

Hello,

There are fields in a table like:
[currencyName] - stores currency name (USD,EUR,...)
[costs] - stores costs

I would like currency formatting of [costs] field to be dependent on
[currencyName] value.

How can this be achieved?


Any suggestions appreciated.

Access 2003
Win XP

 

[Marshall Barton]

There are fields in a table like:
[currencyName] - stores currency name (USD,EUR,...)
[costs] - stores costs

I would like currency formatting of [costs] field to be dependent on
[currencyName] value.

You should provide a few examples of the various kinds of
formatting you want to display.

Are the amount values in that currency or do they need to
be converted?

 

[jaworski_m]

Thank you for answer.

You should provide a few examples of the various kinds of
formatting you want to display.

[costs] field is of type "number".
[currencyName] field is of type "text"

If for example:
1) [costs]=100
[currency]="USD"
2) [costs]=200
[currencyName]="EUR"

I want [cost] field to be formatted on a report:
1) $100
2) €200

Maybe there's another tricky way to abtain this without using format function?

There are fields in a table like:
[currencyName] - stores currency name (USD,EUR,...)
[costs] - stores costs

I would like currency formatting of [costs] field to be dependent on
[currencyName] value.

You should provide a few examples of the various kinds of
formatting you want to display.

Are the amount values in that currency or do they need to
be converted?

 

[Marshall Barton]

You should provide a few examples of the various kinds of
formatting you want to display.

[costs] field is of type "number".
[currencyName] field is of type "text"

If for example:
1) [costs]=100
[currency]="USD"
2) [costs]=200
[currencyName]="EUR"

I want [cost] field to be formatted on a report:
1) $100
2) €200

Maybe there's another tricky way to abtain this without using format function?

It looks like you just want the symbol that goes with the
text. In that case I suggest that you create a table with
two fields and one row for each currency:

Table: Currencies
CurrencyName Text
Symbol Text

Then you can use a query to join that table to your costs
table on the CurrencyName field and include the Symbol field
in the query's field list. With this arrangement, you can
just concatenate the symbol to a formatted number. E.g.
=Symbol & Format(cost, "#,##0.00")