Dlookup frustration, multiple criteria

[matt beamish]

Hello forum people.
Please can someone help me with a Dlookup - I've spent a few hours on it now, and I am going around in circles.
My criteria are [FullAccession] which is a string, and [Context] which is an integer.

This works:

=DLookUp("[Title]","[Q_DiggitContexts]","[ContextInt] = [Context]")

And this works

=DLookUp("[Title]","[Q_DiggitContexts]","[FullAccession]= " & Chr(34) & [FullAccessioncntrl] & Chr(34))

But this doesn't

=DLookUp("[Title]","[Q_DiggitContexts]","([FullAccession]= " & Chr(34) & [FullAccessioncntrl] & Chr(34) And "[ContextInt] = [Context]")

I've tried adding brackets but not correctly as get an error

Any help and explanation of where I am going wrong, as ever, appreciated.

thanks

 

[Isaac]

One thing that may be helpful to know, you can skip the Chr(34) and instead put a literal single quote, inside your quoted text, so the 'this works' one becomes

=DLookUp("[Title]","[Q_DiggitContexts]","[FullAccession]= '" & [FullAccessioncntrl] & "'")

(Access QBE propogates a myth that double quotes are required for quoted text, but actually single quotes work in this case)

as to the problem, is Context really a number? (the only way it would work without quotes or some delimiter)
Also you're missing the And being inside quotes.

 

[Gasman]

I always reccommend to put all the criteria into a string variable and debug.print that until you get it correct. Then you can use it in the functions.
Also if for any reason you still cannot get it correct, you can post the output back here.

Strings need to be surrounded by single quotes ' unless it contains a single quote, then triple double quotes works, I think?

Date literals with # and in mm/dd/yyyy or yyyy-mm-dd format

Numbers do not need anything

Taking what you produced

=DLookUp("[Title]","[Q_DiggitContexts]","([FullAccession]= " & Chr(34) & [FullAccessioncntrl] & Chr(34) And "[ContextInt] = [Context]")

Why do you concatenate FullAccessioncntrl and not Context?
I would also use the me. prefix for the controls.

 

[plog]

Gotta think like a computer. Actually, you have to think like two different computers at once.

The last computer evaluates a logic string (the criteria of your Dlookup). When you want to concatenate 2 different rules of logic together you must use ' AND ' between them for this computer to understand.

The first computer compiles that criteria string to send to the second computer. When you want to concatenate 2 different pieces of strings together you must use '&' between them for this computer to understand.

So, you're error is in the first computer---compiling the string itself. Look at the 'AND' in your criteria:

"([FullAccession]= " & Chr(34) & [FullAccessioncntrl] & Chr(34) And "[ContextInt] = [Context]")

The first computer doesn't use AND as an operation, so it has no idea what you are talking about. That AND is meant for the second computer, so it must be inside quote marks.

"([FullAccession]= " & Chr(34) & [FullAccessioncntrl] & Chr(34) & "And [ContextInt] = [Context]")

 

[June7]

What is Context and why is not concatenated?

Domain aggregates can cause slow performance in query and on form if large dataset is involved.

Want to provide db for analysis?

 

[plog]

Just noticed you missed a closing parenthesis. So the Dlookup has one and needs one--that starts right after DLookup and closes with the very last character.

The one you don't need starts before [FullAccession] in the criteria string and is not needed, so simply remove it:

=DLookUp("[Title]","[Q_DiggitContexts]","[FullAccession]= " & Chr(34) & [FullAccessioncntrl] & Chr(34) & " And [ContextInt] = [Context]")

 

[The_Doc_Man]

The problem with ALL domain aggregate functions is to visualize what it is going to do internally. I'm not saying it ACTUALLY does this, but it helps you to visualize what is going on if you imagine this happens.

You have a domain aggregate function. Since you were using DLookup, we can do that one. The arguments of any of the aggregates are pretty much the same. Using DLookup you have DLookup( "expr", "domain", "criteria" ) so imagine this function:

Public Function DLookup( expr As String, domain As String, criteria As String) As Variant
    Dim DSQL As String, DRS As DAO.Recordset

    DSQL = "SELECT FIRST " & expr & " AS QRESULT FROM " & domain & " WHERE " & criteria & " ;"
    Set DRS = CurrentDB.OpenRecordset( DSQL, dbOpenDynaset )
    DLookup = DRS!QRESULT
    DRS.Close

End Function

I'm not saying that this is actually what the domain functions do, but you can imagine it happening this way. Which means you now know what would be OK for each argument. The "expr" part can be an expression because SQL would allow an expression - but if it IS an expression, any parentheses have to be balanced, and the field-name rules would apply to bracketing requirements. The "domain" part HAS to be something that can produce a recordset that can be queried using DAO's OpenRecordset function. So it has to be a table or a stored query. The "criteria" part has to fit in with being part of a WHERE clause - minus the word "WHERE" because the function provides it internally. Again, as long as you have valid parentheses and quoting to fit in with the implied WHERE clause, you are good to go.

Just for the record, I'm sure the REAL domain functions include argument checking and error handling - but that isn't required for this discussion.

EDITED BY The_Doc_Man to simplify the code. 2/14/2025

 

[matt beamish]

DLookUp("[Title]","[Q_DiggitContexts]","[FullAccession]= " & Chr(34) & [FullAccessioncntrl] & Chr(34) & " And [ContextInt] = [Context]")

Thank you very much everyone for your speedy and helpful responses - the line above is the solution.
Context is an integer although it's use is only as an identifier - but as far as storage and formating go, it as a long integer. Sometimes I format it as text and use it as such.
Re Chr(34) and the strings - I've only recently switched to this long hand way but my eyes are getting old and tired and I find that this way I struggle less with small fonts and distinguishing the ' and " that appear in the 'build' dialogue - I always wish the dialogue would respond to a zoom wheel. More often these days, I copy these bits of text out into notepad or onenote where I can zoom them up and tinker, before pasting back.
My fault was clearly with not understanding that I need to be clear about AND - and not make an assumption that the word has an implicit meaning - so I am educated
thanks again
Matt

 

[matt beamish]

I always reccommend to put all the criteria into a string variable and debug.print that until you get it correct. Then you can use it in the functions.
Also if for any reason you still cannot get it correct, you can post the output back here.

Yes I can see this would help - thanks

 

[GPGeorge]

Thank you very much everyone for your speedy and helpful responses - the line above is the solution.
Context is an integer although it's use is only as an identifier - but as far as storage and formating go, it as a long integer. Sometimes I format it as text and use it as such.
Re Chr(34) and the strings - I've only recently switched to this long hand way but my eyes are getting old and tired and I find that this way I struggle less with small fonts and distinguishing the ' and " that appear in the 'build' dialogue - I always wish the dialogue would respond to a zoom wheel. More often these days, I copy these bits of text out into notepad or onenote where I can zoom them up and tinker, before pasting back.
My fault was clearly with not understanding that I need to be clear about AND - and not make an assumption that the word has an implicit meaning - so I am educated
thanks again
Matt

Re: Zoom. Try [Shift]F2 to open a zoom box on the control or dialogue which has the focus.

 

[MajP]

If you want an easier, more robust, and more flexible way to do this and still get the visibility
You can use the CSql function in the thread.

Get Filter from Controls, CSQL, and Combine Multiple Filters

If you follow my threads, I try to demonstrate how to write code that is flexible, generic, encapsulated, fault proof, and debuggable. My goal is always to try to write code once that can be re-used often and everywhere. I do this in functions, procedures, and class modules. This may require...

www.access-programmers.co.uk

More importantly it handles dates, numerics, strings, booleans and nulls.

"[FullAccession]= " & Chr(34) & [FullAccessioncntrl] & Chr(34)
to
"[FullAccession]= " & csql([FullAccessioncntrl])

 

[arnelgp]

also:

=DLookUp("[Title]","[Q_DiggitContexts]","[FullAccession]= '" & Replace$([FullAccessioncntrl],"'","''")  & "' And [ContextInt] = " & [Context])

 

[Pat Hartman]

=DLookUp("[Title]","[Q_DiggitContexts]","[ContextInt] = [Context]")

Are you sure?
Where is the value of Context coming from? you are comparing ContextInt to Context from the same row. Context is not a form field.

=DLookUp("[Title]","[Q_DiggitContexts]","([FullAccession]= " & Chr(34) & [FullAccessioncntrl] & Chr(34) And "[ContextInt] = [Context]")

It doesn't work because you have no AND. You are confusing the And with a concatenation operator.

=DLookUp("[Title]","[Q_DiggitContexts]","([FullAccession]= " & Chr(34) & [FullAccessioncntrl] & Chr(34) & " And [ContextInt] = [Context]") - - assuming the Context part actually makes sense.

Sometimes I format it as text and use it as such.

Use the data types correctly and you are less likely to have trouble.

When you are building concatenated strings, it is very difficult to get all the punctuation in the right places. The BEST solution (even experts do it) is to always build concatenated strings into a variable. That way you can use the variable in place of the string, PLUS, you can put a stop in your code and print out the variable to see how Access followed your concatenation code. You almost always see the misplaced/missing element. This is debugging #1 in your lesson book.