Solved

DLook with Multiple Criteria

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

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
Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

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

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Alter an update query which rounds 7 29
Compile Error 7 40
Two list boxes - best structure 3 31
Why doesn't duplicate values work on this spreadsheet? 6 31
Deploying a Microsoft Access application in a Citrix environment is not difficult but takes a few steps. However, Citrix system people are often of little help, as they typically know next to nothing about Access. The script provided here will take …
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…
Learn how to make your own table of contents in Microsoft Word using paragraph styles and the automatic table of contents tool. We'll be using the paragraph styles in Word’s Home toolbar to help you create a table of contents. Type out your initial …

932 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

13 Experts available now in Live!

Get 1:1 Help Now