?
Solved

DLook with Multiple Criteria

Posted on 2014-02-06
11
Medium Priority
?
363 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Moving data to the cloud? Find out if you’re ready

Before moving to the cloud, it is important to carefully define your db needs, plan for the migration & understand prod. environment. This wp explains how to define what you need from a cloud provider, plan for the migration & what putting a cloud solution into practice entails.

 

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

Office 365 Training for Admins - 7 Day Trial

Learn how to provision tenants, synchronize on-premise Active Directory, implement Single Sign-On, customize Office deployment, and protect your organization with eDiscovery and DLP policies.  Only from Platform Scholar.

Question has a verified solution.

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

This article describes how to import an Outlook PST file to Office 365 using a third party product to avoid Microsoft's Azure command line tool, saving you time.
This article helps those who get the 0xc004d307 error when trying to rearm (reset the license) Office 2013 in a Virtual Desktop Infrastructure (VDI) and/or those trying to prep the master image for Microsoft Key Management (KMS) activation. (i.e.- C…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
If you’ve ever visited a web page and noticed a cool font that you really liked the look of, but couldn’t figure out which font it was so that you could use it for your own work, then this video is for you! In this Micro Tutorial, you'll learn yo…
Suggested Courses

770 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