?
Solved

DLook with Multiple Criteria

Posted on 2014-02-06
11
Medium Priority
?
365 Views
Last Modified: 2014-02-06
I am working on an Access 2013 db and I am trying to populate some text boxes bases on a dlookup statement.

I have the first part working fine but I can't seem to get it to work with multiple criteria.

=DLookUp("[GLNumber]","Qry_gl","[custname] = '" & [Forms]![Frm_Productiondata]![CustName] & "'")

This is working.  Then next criteria should be the Query Qry_GL description field should = newsprint.  I can't seem to get all the quotes in the correct order to make this work.  Newsprint is text and not coming from the form.
0
Comment
Question by:Mwvarner
  • 6
  • 4
11 Comments
 
LVL 28

Expert Comment

by:MacroShadow
ID: 39839158
DLookup doesn't work with Multiple Criteria.
0
 

Author Comment

by:Mwvarner
ID: 39839162
I've seen dozens of examples on this site.  I just can't get it to work the way I want it to.
0
 
LVL 28

Expert Comment

by:MacroShadow
ID: 39839177
Sorry, does this work:
DLookup("[GLNumber]", "Qry_gl", "[custname] = '" & [Forms]![Frm_Productiondata]![custname] & "'" And [custname] = "newsprint")

Open in new window

0
Fill in the form and get your FREE NFR key NOW!

Veeam is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

 

Author Comment

by:Mwvarner
ID: 39839194
I had to change the field name to description in the second criteria.  But it still fails.  I get the following error when I run the macro.

The object doesn't contain the automation object description.
0
 

Author Comment

by:Mwvarner
ID: 39839198
Do I need to specify the query and the filed name in the statement?
0
 
LVL 28

Expert Comment

by:MacroShadow
ID: 39839210
Sorry once again:
DLookup("[GLNumber]", "Qry_gl", "[custname] = '" & [Forms]![Frm_Productiondata]![custname] & "' And [description] = newsprint")

Open in new window

0
 

Author Comment

by:Mwvarner
ID: 39839221
That fills the text box with the following text.

DLookup("[GLNumber]", "Qry_gl", "[custname] = '" & [Forms]![Frm_Productiondata]![custname] & "'" And "[description] = newsprint")
0
 
LVL 28

Expert Comment

by:MacroShadow
ID: 39839237
The record source has to be set with the = operator
=DLookup("[GLNumber]", "Qry_gl", "[custname] = '" & [Forms]![Frm_Productiondata]![custname] & "' And [description] = newsprint")

Open in new window


btw, you shouldn't have the double quote before [description]
0
 
LVL 120

Accepted Solution

by:
Rey Obrero (Capricorn1) earned 2000 total points
ID: 39839258
almost there, but you need to enclosed the newsprint in quotes


=DLookup("[GLNumber]", "Qry_gl", "[custname] = '" & [Forms]![Frm_Productiondata]![custname] & "' And [description] = 'newsprint'")
0
 

Author Comment

by:Mwvarner
ID: 39839259
Adding the = corrected that error and I don't get an error when it runs but it appears to be ignoring the second criteria and always uses the first record that matches the first criteria.

When I delete the double quotes before description I get an invalid value for value argument error.
0
 

Author Closing Comment

by:Mwvarner
ID: 39839283
That is exactly what I needed.  Thanks.
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

With its various features, Office 365 can not only help you with your day-to-day business tasks, it can also do wonders for your marketing campaign.
Windows Explorer let you handle zip folders nearly as any other folder: Copy, move, change, and delete, etc. In VBA you can also handle normal files and folders, but zip folders takes a little more - and that you'll find here.
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

839 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question