Solved

DLook with Multiple Criteria

Posted on 2014-02-06
11
359 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 27

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 27

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
Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

 

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 27

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 27

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 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

Salesforce Made Easy to Use

On-screen guidance at the moment of need enables you & your employees to focus on the core, you can now boost your adoption rates swiftly and simply with one easy tool.

Question has a verified solution.

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

Outlook Free & Paid Tools
It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
The viewer will learn how to simulate a series of coin tosses with the rand() function and learn how to make these “tosses” depend on a predetermined probability. Flipping Coins in Excel: Enter =RAND() into cell A2: Recalculate the random variable…
The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …

830 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