Dlookup will not pull needed value from query

[hrreece]

I am trying to use the Dlookup expression in a macro to pull the value from the "Price" field in the following query. The query works fine and the Dlookup function did as well until I added a new field to the "Contracts" table called "ContractYear".

SELECT Contracts.ID, Contracts.Commodity, Contracts.Date, Contracts.ContractYear, Contracts.Option, Contracts.[Contract No], Contracts.Bushels, Contracts.Price, Contracts.[Bushels Remaining]
FROM Customer INNER JOIN Contracts ON Customer.ID = Contracts.ID
GROUP BY Contracts.ID, Contracts.Commodity, Contracts.Date, Contracts.ContractYear, Contracts.Option, Contracts.[Contract No], Contracts.Bushels, Contracts.Price, Contracts.[Bushels Remaining]
HAVING (((Contracts.ID)=[Forms]![Transactions]![Customer ID]) AND ((Contracts.Commodity)=[Forms]![Transactions]![Commodity]) AND ((Contracts.ContractYear)<=Year([Forms]![Transactions]![Date])) AND ((Contracts.Option)=[Forms]![Transactions]![Option]) AND ((Contracts.[Bushels Remaining])>0))
ORDER BY Contracts.ID, Contracts.Commodity, Contracts.Date;
 

The Dlookup expression that had previously worked follows.

DLookUp("[Price]","Open Contracts","[Option]='" & [Forms]![Transactions]![Option] & "'" & " And [Commodity]='" & [Forms]![Transactions]![Commodity] & "'" & " And [ID]='" & [Forms]![Transactions]![Customer ID] & "'")
 

When I try to add the extra code for the ContractYear field, I get an error that the expression is too long. I may not need all the criteria in the Dlookup expression, but at this point are at a lost as what to do. The query itself pulls the data I need and as long as Dlookup pulls the price from the first record generated by the query I should be OK.

Any assistance is appreciated.

Thank you.

Roger

[Rabbit]

Please use Code tags.

It seems to me that you've already included all the criteria in the query. There should be no need to include the criteria again in the DLookup.

[hrreece]

If the "Open Contracts" query already has the criteria specified then would I use the DLookup expression as shown below? When I tried it yesterday using this format, it didn't seem to work. If the query finds more than one record, how does DLookup know which record to pull the "Price" information from? Is it the first record?

code:

DLookUp("[Price]","Open Contracts")

Thanks

Roger

[Rabbit]

You might have to use this:
DLookUp("[Price]","[Open Contracts]")

But yes, if the query has all the criteria you need, you don't need to include them in the DLookup if you're going to lookup in the query.

If there are multiple records that match, then it will return the first one it finds.