Avatar of shawnkimble
shawnkimbleFlag for United States of America asked on

Multiple Criteria Dlookup Syntax in Access Query

I have the following two criteria needed in a Dlookup.  The below syntax works correctly but returns the incorrect answer because I cannot figure out how to properly put both criteria in one statement.  

In case its hard to see, In the second statement there are single quotes around BillPaymentCheck.

Expr2: DLookUp("TxnDate","linkedtxndetail",[linkedtxndetail]![IDKEY]=[Bill]![TxnID])
Expr2: DLookUp("TxnDate","linkedtxndetail","[linkedtxndetail]![TxnType]='BillPaymentCheck'")
Microsoft AccessSQL

Avatar of undefined
Last Comment
DatabaseMX (Joe Anderson - Former Microsoft Access MVP)

8/22/2022 - Mon

Expr2: DLookUp("TxnDate","linkedtxndetail",[IDKEY]=[Bill]![TxnID] AND [TxnType]='BillPaymentCheck'"))

OM Gang
DatabaseMX (Joe Anderson - Former Microsoft Access MVP)

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question

Ah, the old Chr(34).  Thanks!

Expr2: DLookUp("TxnDate","linkedtxndetail","[linkedtxndetail]![IDKEY]= " & Chr(34) & [Bill]![TxnID] & Chr(34) & " AND " & "[linkedtxndetail]![TxnType] = " & Chr(34) & "BillPaymentCheck" & Chr(34))
DatabaseMX (Joe Anderson - Former Microsoft Access MVP)

I use Chr(34) for clarity of posting - like ... how many double quotes do you see here:



I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck

Freakin' amazing!  That's the second time in a couple of weeks you've gotten the points on a Q for essentially the exact same answer I posted first.

shawnkimble states
"The below syntax works correctly..."
I provided an solution to the specific question he asked
"...because I cannot figure out how to properly put both criteria in one statement"

The first criteria on [TxnID] should be numeric because he stated the syntax worked correctly.  The use of single apostrophe's was in his original example.  The solution is to use an 'AND' statement to specify multiple cirteria.

I am impressed.  It must be a personality thing 'mx.....I think people just like you better.  Or perhaps I should shower more often.

OM Gang
DatabaseMX (Joe Anderson - Former Microsoft Access MVP)

"essentially the exact same answer I posted first."

Well ... I hate to say this, but ... you missed some double quotes here:

Expr2: DLookUp("TxnDate","linkedtxndetail",[IDKEY]=[Bill]![TxnID] AND [TxnType]='BillPaymentCheck'"))

                                                                 ----^                                     --^  

I missed one double quote (the second).  As I stated, shawnkimble indicated the syntax he provided worked correctly - since he used no double quotes in the first example it was not for me to add them.  One could also argue that you inserted Chr(34) where inappropriate - If [TxnID] is numeric it should not be enclosed in quotes.

Understand that I am not upset with you 'mx - simple frustration with how answers are accepted, i.e.

how do if fix this specific problem?
Answer1 = here's the solution
Answer2 = here's the solution and by the way your shoes are untied

Answer2 accepted - thanks! I could have tripped!

OM Gang
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.

Perhaps from now on I should include a recipe for low-fat brownies with each solution - you know, a value-added kind of thing.
DatabaseMX (Joe Anderson - Former Microsoft Access MVP)

I think you missed more than one ...