Currency Conversion 2

[swtrader]

I'm not sure of the easiest approach. Suggestions, please.

I am using Access as a 'report writer' to create reports out of data extracted from a mainframe db.

The mainframe data includes a name, a monthly account balance and the currency that the account balance is in (Euros, pesos, etc.).

After importing into Access, I need to convert the balances into US dollars.

I have created an Access table: tblExchangeRates with fields pkExchRateID, Currency (Euro, Peso, etc.), EffDateOfExchRate, and ExchRate.

The EffDateOfExchRate will always be the last day of the month as will the date of the account balance.

The tbl tblAcctBalances will have several hundred records for each month. Therefore, I need to 'match' the correct exchange rate with the correct date for the account balance and then multiply the exchange rate X the balance.

I have tried a "Switch" function but I don't know how to limit the exchange rate to the properly dated one. Perhaps a DLookup? (Have never used this before so explanation would be helpful.)

Ideas??

Thanks all for your input.

swtrader
-- The trouble with doing something right the first time is that no one appreciates how hard it was.

 

[Zion7]

From the tblAcctBalances form,

Dim curExChRate As Currency

curExChRate = DLookUp("ExchRate","tblExchangeRates ", _
"EffDateOfExchRate =#" & Me.AcctBlnceDate & "#")

Me!Rate = curExChRate * Me!AcctBalance

 

[Golom]

Or if you want to do it in SQL

Select B.AcctBalance, B.BalanceDate,
       (B.AcctBalance * E.ExchangeRate) As [US Dollars]

From tblAcctBalances As B INNER JOIN tblExchangeRates As E
     ON B.CurrencyType = E.CurrencyType

Where E.ExchangeDate = 
      (Select MAX(ExchangeDate) From tblExchangeRates As X
       Where X.ExchangeDate <= B.BalanceDate)

[small]No! No! You're not thinking ... you're only being logical.
- Neils Bohr[/small]

 

[swtrader]

I didn't get the DLookup past the debugger. Guess I still need to understand how this works.

Hadn't thought of a SQL approach. It worked perfectly.

Thanks to you both.


swtrader
-- The trouble with doing something right the first time is that no one appreciates how hard it was.

 

[Zion7]

Nice work Golom, wish I had thought of that!

 

[Golom]

... it worked perfectly

Well ... it shouldn't have. I missed something

Select B.AcctBalance, B.BalanceDate,
       (B.AcctBalance * E.ExchangeRate) As [US Dollars]

From tblAcctBalances As B INNER JOIN tblExchangeRates As E
     ON B.CurrencyType = E.CurrencyType

Where E.ExchangeDate = 
      (Select MAX(ExchangeDate) From tblExchangeRates As X
       Where X.ExchangeDate <= B.BalanceDate
         [COLOR=red]AND X.CurrencyType = B.CurrencyType[/color])

[small]No! No! You're not thinking ... you're only being logical.
- Neils Bohr[/small]

 

[swtrader]

My 'version' worked perfected (based on your well-thought-out input). I had added the "AND".

swtrader
-- The trouble with doing something right the first time is that no one appreciates how hard it was.