DLookup problem

[TrollBro]

I'm stuck even after looking through other DLookup posts for solutions.

I have a table of exchange rates and a table of activity dates. I want to get the exchange rate for each activity date. However, in any given month, not every day will have an exchange rate, and not every day would have an activity. I want to get the actual rate for the activuty date if there s one, else get the rate from the most recent prior date where a rate existed.

Exchange date, rate
1/2/2009 1.20
1/5/2009 1.18
1/6/2009 1.175

Activity date, desired rate
1/3/2009 1.20
1/4/2009 1.20
1/6/2009 1.175

This gives me the correct date that I need to use for the exchange rate:

Expr2: (SELECT Max(Dat00_eu.date) FROM Dat00_eu WHERE [Dat00_eu]![Date]<=[tblTemplate]![DATE OF Activity]

However, I need the rate, not the date, so I tried this:

Expr2b: DLookUp([fxeurodbl],"Dat00_eu",[Dat00_eu]![Date]<=(SELECT Max(Dat00_eu.date) FROM Dat00_eu WHERE [Dat00_eu]![Date]<=[tblTemplate]![DATE OF Activity])

I get the correct rates only when there is an exact match to a rate date. Where I actually need the rate based on prior dates (because there is no matching rate for the currect date), I keep getting an #Error value.

Does anyone have any suggestions on how I could get this to work?

Thanks

 

[MajP]

No need for a dlookup. There are many queries that can do all of this in one step. Here is one:

SELECT 
 tblActivity.activityDate, 
 tblActivity.desiredRate, 
 tblExchangeRate.exchangeDate, 
 tblExchangeRate.exchangeRate
FROM 
 tblExchangeRate, 
 tblActivity
WHERE 
 (tblExchangeRate.exchangeDate) In (select max([exchangeDate]) from tblExchangeRate where tblActivity.activityDate >= tblExchangeRate.exchangeDate)

Results

actDate	  desRate exchangeDate	exchangeRate
1/3/2009  1.2     1/2/2009      1.2
1/4/2009  1.2     1/2/2009      1.2
1/6/2009  1.175   1/6/2009      1.175

 

[TrollBro]

Thanks MajP

My example may have been confusing. My tblActivity does not contain a field called desiredRate. DesiredRate would represent the result I am trying to derive, so it would be a filed I create in the qry only.

So, Activity table has a [ActivityDate] field and an [EuroAmount] field. my tblExchangeRate has the [exchangeDate] and [exchangeRate] fields.

qry would then have the activity date, activity amount, exchangerate date, exchangerate rate, and expression containing what would be the desired rate. I hope I made this clearer rather than mod confusing.

P.s. even if I take out the tblActivity.desiredRate in the select statement, the qry takes quite a long time to run against 150k records.

Any thoughts?

Thanks!