Filtered recordset alternative

[Roland Greve]

Hi all,

I'm currently looping through a filtered recordset to find an exchange rate
on a given date. This is working ok, but it's getting slower and slower as
the exchange rate table grows.

My tblExchangeRates looks like this (local currency is EUR):
ValidityDate ForeignCurrency ExchangeRate
15-07-03 USD 1,1328
31-07-03 USD 1,1464
28-08-03 USD 1,1283
15-07-03 AUD 1,7171
31-07-03 AUD 1,7316

What would be the quickest way to return the last known exchange rate for a
particular currency on a given date. The function FindMyExchangeRate("USD",
"17-08-03") should return 1,1464 (the USD exchange rate on the last known
date "31-07-03" before or equal to the given date "17-08-03").

I hope this is somewhat clear.

Regards,
Roland

 

[Douglas J. Steele]

Put another field in the table, indicating when the rate stopped being
effective (i.e.: the end date will be the same as the start date for the
next record). Use Null for the end date for the current rate.

Your WHERE clause will be something like
WHERE ValidityDate <= DateInQuestion
AND ((EndingDate > DateInQuestion) OR EndingDate IS NULL)

That should return a single row, being the rate in effect on DateInQuestion

I simplified the expression above. Assuming you're going to be accepting
input from a text box and building the query dynamically to determine the
rate, you'll need to be careful, since you're obviously using dd/mm/yyyy
format as your standard, and Access can have problems with that.

 

[Roland Greve]

Thanks!

Regards,
Roland

Put another field in the table, indicating when the rate stopped being
effective (i.e.: the end date will be the same as the start date for the
next record). Use Null for the end date for the current rate.

Your WHERE clause will be something like
WHERE ValidityDate <= DateInQuestion
AND ((EndingDate > DateInQuestion) OR EndingDate IS NULL)

That should return a single row, being the rate in effect on DateInQuestion

I simplified the expression above. Assuming you're going to be accepting
input from a text box and building the query dynamically to determine the
rate, you'll need to be careful, since you're obviously using dd/mm/yyyy
format as your standard, and Access can have problems with that.

--
Doug Steele, Microsoft Access MVP

(No private e-mails, please)



for