Conditional criteria in query

[dfuas]

Hi,

I have a query in which I try to calculate field values depending on the currency and foreign exchange rates.
I have a currency field with the amount value and a combo box where I select the currency for that amount which can be (USD, Euro, BRL, or INR). I have got also another 4 fields with each of currency foreign exchange rates.
I am trying to obtain a new calculated field of Price in Euros if the amount I have in the 1st field is not already in Euros.

Example:

Say:

Field 1-------amount


Field 2---------currency (can be in USD or Euro or BRL or INR
depending on a refNo)

Field 3-------------xchange rate


Field of Results-------------field1 * field 3 in Euro depending
on the currency in field 2.


My understanding is that it can only be done using vba and I am not very familiar with vba. Can anyone help, please?

thank you

dfuas

 

[namliam]

You can do it in vba offcourse, but it can also be done in the query.

Assuming you have a table which contains the exchange rates for every (including EURO => 1.00) then its very easy.
Just add that table to your query.
Link it on the currency field.
Now you have your exchange rate (if you dont allready)
Now in your result field simply put the formula:
EUROAMOUNT: [rate] * [amount]

done...

Good Luck

 

[dfuas]

What you say is true and I rather do it in the query than vba as I have really no clue how to start with it in vba, but the problem is:

EUROAMOUNT: [rate] * [amount]

the [amount] field is a dropdown currency which can be in 4 currencies (USD, Euro, BRL and INR)

the [rate] are four checkboxs with each rate of the above currency.

I appreciate your help.

dfuas

 

[namliam]

the [amount] field is a dropdown currency which can be in 4 currencies (USD, Euro, BRL and INR)

the [rate] are four checkboxs with each rate of the above currency.

??
You have rates in checkboxes??? Uhm??

If I were you I would create a table that contains the 4 currencies + rates.
This table you can use to populate your dropdown and autofetch the rate both in you query and in your form (instead of checkboxes?)

Greetz