Scratching my head

[geoffcodd]

Hi there,

This is probably me just being thick, but I have a problem with the following formula

Unit_Cost: DLookUp("[Unit_Cost]","tblTariff","[Tariff_Name] = """ & [Tariff] & """ And [Tariff_Date] <= #" & [Month_Pres] & "#")

It should look up the unit cost related to the Month_Pres

eg Month_Pres = 09/10/2001 Unit_Cost = 0.008540
Month_Pres = 07/09/2002 Unit_Cost = 0.010012

Tariff_Name Tariff_Date Unit_Cost
Interruptible_Gas 01/08/2000 0.007941
Interruptible_Gas 01/08/2001 0.008540
Interruptible_Gas 01/08/2002 0.010012
Interruptible_Gas 01/08/2003 0.010242

At the moment it returns 0.007941 for each record.

Any ideas anyone

Thanks
Geoff

 

[Rich]

In your example all the first date shown is 01/08/00, your criteria is looking for dates less than or equal to 09/10/2001 or any date you enter, it should either = or >=

 

[geoffcodd]

Now I get the following results

Month_Pres = 09/10/2001 Unit_Cost = 0.010012 Should be 0.008540

And

Month_Pres = 07/09/2002 Unit_Cost = 0.010242 Should be 0.010012

Any other sugestions

Thanks
Geoff

 

[geoffcodd]

I know what I need to do, but I cant't seem to get it to work, the current formula I have is

Unit_Cost: DLookUp("[Unit_Cost]","tblTariff","[Tariff_Name] = """ & [Tariff] & """ And [Tariff_Date] >= #" & [Month_Pres] & "#")

But instead of it using [Month_Pres] as the criteria for the date field I need to use

DMax("[Tariff_Date]","tblTariff","[Tariff_Name] = """ & [Tariff] & """ And [Tariff_Date] <= #" & [Month_Pres] & "#")

But I can't seem to incorporate it into the formula

Any ideas

Thanks
Geoff

 

[Rich]

I would actually prefer to use a query with the criteria set to some selection on a form and then use a simple DLookup on the query, although on reflection a combo to return the value would be even better