calculating depending on the currency & f/x rate

[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 in a new calculated field of Price in Euros if the amount I have in the 1st field is not already in Euros.

Say:

Field 1 Field 2 Field 3
amount currency F/rate


2000 in USD or Euro or BRL or INR 0.8754
depending what is selected
in combo box.


Field 3

Result

field1 * field 3 in Euro depending currency selected in field 2

Can anyone help, plese?

thank you

dfuas

 

[Bat17]

Do the name and xchange rate in the combo. Base the combo on a table, one field of which is the rate the other name of the currency.
use the rate field as the first column in the combo and hide it. then you just need cost * combo

Hope that makes sense!

Peter

 

[dfuas]

sorry I don't understand this.
the name is already in a combo. so with the amount I select the name of the currency and if is not already Euro it checks the values in xchange rate fields corresponding to the name of currency in the combo and the does the calculation depending on that. In one field you could have USD and another BRL but they will need to be calaculated in Euros with their corresponding xchange rates.

 

[Bat17]

Unless you actualy need to see all the options/rates available at once then you method will make hard work for you. If the 'boss' decides he wants another currency added then you will have to edit the form and hack the code to get it working. You will also have to hack anything else that depends on the same structure. Doing it with a table/combo box you just need to add another entry to the table to add another option.
I will knock up a little demo to show you what I mean.
You may also want to do a seach of the forum fo 'normalisation' to see disccusions on the best/correct methods of using data.

HTH

Peter

 

[dfuas]

Thanks a lot. I could see the attachment, but the problem I have is that the rates are in a table and are all in displayed different textboxes, because I have another table of trade data with autonumber RefNo and each RefNo could have any of the 4 currencies. So on the calculation I need to check the currency for say RefNo 1 and check the xchange rate for conversion to Euro if the currency in RefNo is not already in Euro.
For instance I could have RefNo1 in BRL, RefNo2 in INR, RefNo3 in USD and RefNo4 in EURO and in my calculated results they all need to be done in Euro.
By the way both tables are already normalised.
Thanks
dfuas

 

[Bat17]

ok then use a select case and do your maths that way. You will need to fire it from an event thou.

Select Case Me.cboCurrency
Case Is = "USD"
    Me.txtResult = RefNo1 * Me.txtAmount
Case Is = "Euro"
    Me.txtResult = RefNo2 * Me.txtAmount
Case Is = "BRL"
    Me.txtResult = RefNo3 * Me.txtAmount
Case Is = "INR"
    Me.txtResult = RefNo4 * Me.txtAmount
End Select

Peter

 

[dfuas]

Sorry on that, but will all the query be based on the vba or can I just obtain the results for that field using the code? can you give some clues, please, all these vba and integrating it with the query is news to me, but where there is a will everything possible. thanks

 

[Bat17]

Sorry you are losing me, I thought you were working in a form not a query.

Peter

 

[dfuas]

No I am working on a query and that query will be used to display the results for a report. but still you input is being helpful. thanks
dfuas

 

[Bat17]

about the only way I can see to do it in a query is to pass the Amount, the currency choice, and the four choices.

Function GetCost(strCurrency As String, dblAmount, RefNo1, RefNo2, RefNo3, RefNo4 As Double)
Select Case strCurrency
Case Is = "USD"
    GetCost = RefNo1 * MdblAmount
Case Is = "Euro"
    MGetCost = RefNo2 * dblAmount
Case Is = "BRL"
    GetCost = RefNo3 * dblAmount
Case Is = "INR"
    GetCost = RefNo4 * dblAmount
End Select
End Function

Peter

 

[dfuas]

thanks, I will try to work on that.
dfuas