Converting currency within a query

[Guest]

Within Access 2003 I currently have a table with currency values in and then
a related price. However i need to convert all of these to Dollar values.
For example I would take Swedish SEK (as called in the database) and divide
this by the conversion rate to give a dollar value. The same would apply for
Euros or British pounds
Any ideas?

Matt

 

[Marshall Barton]

Within Access 2003 I currently have a table with currency values in and then
a related price. However i need to convert all of these to Dollar values.
For example I would take Swedish SEK (as called in the database) and divide
this by the conversion rate to give a dollar value. The same would apply for
Euros or British pounds

The query needs to include both tables. Here's the general
idea:

SELECT f1, f2, price, table.curcode,
price/convrate As Dollars
FROM table INNER JOIN convrates
ON table.curcode = convrates.curcode

 

[Guest]

Marsh,

Sorry, as a relative beginner, i dont really understand the principle.
Can you not do a formula within the criteria that says something along the
lines of IF [Currency] = "SEK", THEN [Price]/7.51

This does not work but how would i make it work that way and where would i
write it within criteria? Within the price criteria?

 

[John Spencer]

Yes, you can do the formula. It is not as flexible and has to be edited if
you add another currency or change the exchange rate.
You can use an Immediate If ( I I F ) in the query.

IIF([Currency]="SEK",[Price]/7.51,IIF([Currency]="Euros",[Price]/2,
IIF(Currency="Pounds",[Price]*5,[Price])) )

Limits: IIF can be nested up to 7 levels. IIF gets tough to follow if you
have many levels.

There is also a SWITCH function (look it up in the VBA help) that is a
little clearer to use.

The best way to handle this is with a table containing the exchange rate for
the currencies.

Marsh,

Sorry, as a relative beginner, i dont really understand the principle.
Can you not do a formula within the criteria that says something along the
lines of IF [Currency] = "SEK", THEN [Price]/7.51

This does not work but how would i make it work that way and where would i
write it within criteria? Within the price criteria?

The query needs to include both tables. Here's the general
idea:

SELECT f1, f2, price, table.curcode,
price/convrate As Dollars
FROM table INNER JOIN convrates
ON table.curcode = convrates.curcode

 

[Marshall Barton]

Sorry, as a relative beginner, i dont really understand the principle.
Can you not do a formula within the criteria that says something along the
lines of IF [Currency] = "SEK", THEN [Price]/7.51

This does not work but how would i make it work that way and where would i
write it within criteria? Within the price criteria?

You can not introduce a value in the criteria.

What I thought you have is a table of currencies and
exchange ratesL

table: ConvRates
fields: CurCode Text (primary key)
ConvRate Currency

Some records in the table would look like:
USD 1
SEK 7.51
EURO 1.23
. . .

Then the query I posted would join the two tables using the
CurCode field. he makes the appropriate ConvRate field
available in each record. This allows you to use a
calculated field in the query for the USDPrice.