Mwvarner
asked on
DLook with Multiple Criteria
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","[cus tname] = '" & [Forms]![Frm_Productiondat a]![CustNa me] & "'")
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.
I have the first part working fine but I can't seem to get it to work with multiple criteria.
=DLookUp("[GLNumber]","Qry
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.
DLookup doesn't work with Multiple Criteria.
ASKER
I've seen dozens of examples on this site. I just can't get it to work the way I want it to.
Sorry, does this work:
DLookup("[GLNumber]", "Qry_gl", "[custname] = '" & [Forms]![Frm_Productiondata]![custname] & "'" And [custname] = "newsprint")
ASKER
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.
The object doesn't contain the automation object description.
ASKER
Do I need to specify the query and the filed name in the statement?
Sorry once again:
DLookup("[GLNumber]", "Qry_gl", "[custname] = '" & [Forms]![Frm_Productiondata]![custname] & "' And [description] = newsprint")
ASKER
That fills the text box with the following text.
DLookup("[GLNumber]", "Qry_gl", "[custname] = '" & [Forms]![Frm_Productiondat a]![custna me] & "'" And "[description] = newsprint")
DLookup("[GLNumber]", "Qry_gl", "[custname] = '" & [Forms]![Frm_Productiondat
The record source has to be set with the = operator
btw, you shouldn't have the double quote before [description]
=DLookup("[GLNumber]", "Qry_gl", "[custname] = '" & [Forms]![Frm_Productiondata]![custname] & "' And [description] = newsprint")
btw, you shouldn't have the double quote before [description]
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.
When I delete the double quotes before description I get an invalid value for value argument error.
ASKER
That is exactly what I needed. Thanks.