IIf expression

[Kells via AccessMonster.com]

Here is the expression I am having trouble with:

Sum(IIf([currency]="AUD",[value]*[AUD],IIf([currency]="CAD",[value]/[CAD],IIf
([currency]="CHF",[value]/[CHF],IIf([currency]="DKK",[value]/[DKK],IIf(
[currency]="EUR",[value]*[EUR],IIf([currency]="GBP",[value]*[GBP],IIf(
[currency]="HKD",[value]/[HKD],IIf([currency]="JPY",[value]/[JPY],IIf(
[currency]="NOK",[value]/[NOK],IIf([currency]="NZD",[value]*[NZD],IIf(
[currency]="SEK",[value]/[SEK],IIf([currency]="SGD",[value]/[SGD],IIf(
[currency]="ZAR",[value]/[ZAR],[value]))))))))))))))


What it means is that If currency type equals “AUD†(australian dollars etc..)
then take the value and multiply/divide by the actual rate to convert it into
US dollars.

It worked fine until I tried to add MXN (Mexican peso) and ILS (Israeli
sheckles), then it says formula is too complex. After a bit of testing I
realised that only 16 IIfs can be used.

Can anyone suggest another way of doing this sum as this expression is only
going to get bigger as in time more currencies will need to be added and it
is stopping me progressing any further with my work.

I am not great using SQL and any help would be greatly appreciated.

Many thanks

Kells

 

[pietlinden]

what if you create a table of (CurrencyType, ExchangeRate) and then
use a join on CurrencyType and multiply by ExchangeRate? The nice
thing about doing it this way is that you can add as many
CurrencyTypes as you want (Currency Type.. e.g. USD, Yen, etc..)

 

[KARL DEWEY]

Use a translation table.

 

[John Spencer]

Add a table with two fields
CountryCode
ConversionFactor

Then you could use it in your query as follows.

SELECT Sum([Value] * ConversionFactor)
YourCurrentTable as Y INNER JOIN ConversionTable as C
On Y.[Currency] = C.CountryCode

When you add a new country, you add a record to the Conversion table. When
the rate changes you change the rate in the Conversion table.

It can get more complex if you need to change things over time and keep a
record of what was the rate, but the basic idea is covered above.

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County