dlookup in query using month/year as criteria (1 Viewer)

[hunter_lake]

hello,

I have a query that I need to look up a fuel rate in a table based on the month and year of the applicable load date.

ActFuel is the table, Rate is a number field in table, FuelDate is a date/time field associated with the rate. LoadDate is a date/time field in the query from another table.

FRate: Dlookup("Rate", "ActFuel", "FuelDate = Month([LoadDate]) AND Year([LoadDate])")

Where am I going wrong?

 

[GanzPopp]

You are making a few mistakes here:
- Comparing a Date field (FuelDate) with a number (Month(LoadDate)) is not possible
- You can't compare one field (FuelDate) with two results at the same time (Month([LoadDate]) AND Year([LoadDate]))
- You are using a secondary condition which is always true, because of the AND operator in the last part of statement (AND Year([LoadDate]))

This is the way conditions are evaluated internally and you should always check whether everything makes sense logically.

There are several ways to achieve the results you want. One way is to change the where condition (third parameter) into:
Month(FuelDate) = Month([LoadDate]) AND Year(FuelDate) = Year([LoadDate]))

 

[hunter_lake]

Thanks GanzPopp, I appreciate the insight

 

[hunter_lake]

This did not solve the problem, running the query returns an error "access cant find the name 'LoadDate' you entered in the expression"? LoadDate is a field in the query I am writing ... epr1: DLookUp("FuelRate","ActFuel","Month(FuelDate) = Month([LoadDate]) AND Year(FuelDate) = Year([LoadDate])"))

You are making a few mistakes here:
- Comparing a Date field (FuelDate) with a number (Month(LoadDate)) is not possible
- You can't compare one field (FuelDate) with two results at the same time (Month([LoadDate]) AND Year([LoadDate]))
- You are using a secondary condition which is always true, because of the AND operator in the last part of statement (AND Year([LoadDate]))

This is the way conditions are evaluated internally and you should always check whether everything makes sense logically.

There are several ways to achieve the results you want. One way is to change the where condition (third parameter) into:
Month(FuelDate) = Month([LoadDate]) AND Year(FuelDate) = Year([LoadDate]))

 

[GanzPopp]

Then the field LoadDate doesn't exist in the table/query ActFuel. You need to check this first. Can you post the field names of ActFuel?

 

[hunter_lake]

The table ActFuel fields are: "InputDate", "FuelDate", "FuelRate"

I am trying lookup the "FuelRate" for a given month/year from "FuelDate", that I can associate with the "LoadDate" of a shipment to calculate the fuel surcharge. "LoadDate" is a field in the query I am creating based on another table. I thought it was a syntax issue to do with the date? I have another field in the query that works, which is basically doing the same thing.

DLookUp("TexFuel","DestPlant","Dest = '" & [Destination] & "'")

 

[hunter_lake]

Anyone have a solution?

 

[hunter_lake]

Decided to go a different route, scrapped the original query with the Dlookup and created a new one, gets the results I was looking for.

SELECT * FROM BargeCost, ActFuel WHERE Month(ActFuel.FuelDate) = Month(BargeCost.LoadDate) AND Year(ActFuel.FuelDate) = Year(BargeCost.LoadDate)

Case closed

Thanks again GanzPopp

 

[narang]

Hello,

You may try the following

FRate: Dlookup("Rate", "ActFuel", "month(FuelDate) = (Month([LoadDate]) AND year([LoadDate])=Year([LoadDate])")

Rgds