By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,972 Members | 911 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,972 IT Pros & Developers. It's quick & easy.

Dlookup will not pull needed value from query

P: 8
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".

Expand|Select|Wrap|Line Numbers
  1. SELECT Contracts.ID, Contracts.Commodity, Contracts.Date, Contracts.ContractYear, Contracts.Option, Contracts.[Contract No], Contracts.Bushels, Contracts.Price, Contracts.[Bushels Remaining]
  2. FROM Customer INNER JOIN Contracts ON Customer.ID = Contracts.ID
  3. GROUP BY Contracts.ID, Contracts.Commodity, Contracts.Date, Contracts.ContractYear, Contracts.Option, Contracts.[Contract No], Contracts.Bushels, Contracts.Price, Contracts.[Bushels Remaining]
  4. 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))
  5. ORDER BY Contracts.ID, Contracts.Commodity, Contracts.Date;
  6.  
The Dlookup expression that had previously worked follows.
Expand|Select|Wrap|Line Numbers
  1. DLookUp("[Price]","Open Contracts","[Option]='" & [Forms]![Transactions]![Option] & "'" & " And [Commodity]='" & [Forms]![Transactions]![Commodity] & "'" & " And [ID]='" & [Forms]![Transactions]![Customer ID] & "'")
  2.  
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
Oct 19 '07 #1
Share this Question
Share on Google+
3 Replies


Rabbit
Expert Mod 10K+
P: 12,349
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.
Oct 19 '07 #2

P: 8
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
Oct 21 '07 #3

Rabbit
Expert Mod 10K+
P: 12,349
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.
Oct 21 '07 #4

Post your reply

Sign in to post your reply or Sign up for a free account.