Help w. Calculated field

[setis]

I have a spreadsheet in excel that I am struggling to translate into access.

In the excel I have:

P2: Currency (f.ex EUR)
R2: Amount to Pay (amount in P2 currency)
S2: Amount in GBP

I have Sheet 1 with the name of the Currencies in Column L and the conversion of 1 unit to GBP in column M. The name of this table is CurrencyExchange

S2 is calculated in excel like this: =IF(P2=Sheet1!$L$3;'Claims Main Sheet'!R2;(R2/(VLOOKUP(P2;CurrencyExchange;2;FALSE))))

Basically the form says that if the currency in P2 is GBP, the result is R2, if not, divide the amount for the correspondent exchange rate from CurrencyExchange.

Could somebody please help me translate this function to access?

 

[Minty]

Create a table with the currency rates and symbols / currency shortcodes, include one for GBP. Join the currency rate symbol or shortcode to your currency field with the amounts to pay.

In the query create a calulated field GBPAmount: [Rate] * [AmountToPay]
Make the rate 1 for GBP and you don't need complicated IIfs etc.

 

[setis]

That was easy. Thank you!

 

[Minty]

You're welcome