Solved

DLook with Multiple Criteria

Posted on 2014-02-06
11
355 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 26

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 26

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
 

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
Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

 
LVL 26

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 26

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 119

Accepted Solution

by:
Rey Obrero earned 500 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

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

Entering a date in Microsoft Access can be tricky. A typo can cause month and day to be shuffled, entering the day only causes an error, as does entering, say, day 31 in June. This article shows how an inputmask supported by code can help the user a…
This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…

758 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now