Dlookup with 2 criteria (1 Viewer)

[jeffatwork]

Im attempting to do a Dlookup in a query where there are 2 criteria for the lookup. Attached is a snapshot of the 2 tables I'm working with. I can get it to work with single criteria, but when I add the 2nd criteria (where), it doesnt work. I realize DMax is likely the solution and this specific query will only return "matching" items, but I'm just attempting to simplify my explanation of the problem. The Dlookup I'm using with 2 criteria, which doesnt work is:

DLookUp("Score","AFF","[start]=" & [Resid] And "[HH_Size]=" & [HHS])

 

[Gasman]

You have " the wrong side of AND and also missing an & to continue the concatenation.
Put criteria into a string variable and debug.print until correct, then use that in the function.

 

[jeffatwork]

You have " the wrong side of AND and also missing an & to continue the concatenation.
Put criteria into a string variable and debug.print until correct, then use that in the function.

Not exactly sure where to put them. I've tried them on the other side without success. Can you show me the statement you think will work? Thanks in advance.

 

[plog]

1. "Doesn't work" is very unhelpful. Produce an error? Shows no results? Shows unexpected results? Caught your computer on fire? Give us more than "doesn't work" to go on.

2. Are you sure you want [start] = [Resid]? Looks like start is the the low range and end is the high range. Do you want Resid between those two? Or just exactly matching the start value?

3. How about we put whatever this issue is aside and focus on what you really want. Its like you choose a method, couldn't get it to work, so branched off to a side method, can't get that to work and you want us to help with an issue that's only parentetically attached to your real goal.

Provide us with 2 sets of sample data to demonstrate your actual issue (not this Dmax/Dlookup problem we are running down):

A. Starting data from your table(s). Include table and field names and enough data to cover all cases.

B. Expected results of A. Show us what data you ultimately hope to end up with when you feed in the data from A.

Again, 2 sets of data--starting and expected results of starting.

 

[cheekybuddha]

Can you show me the statement you think will work?

DLookUp("Score","AFF","[start]=" & [Resid] And "[HH_Size]=" & [HHS])
                                               ^
                                               |
                                    This needs to move to here:
                                           |
                                           v
DLookUp("Score","AFF","[start]=" & [Resid] " And [HH_Size]=" & [HHS])
                                           ^
                                           |
                                You also need another '&':
                                           |
                                           v
DLookUp("Score","AFF","[start]=" & [Resid] & " And [HH_Size]=" & [HHS])

 

[cheekybuddha]

gasman suggests you use a string variable like this:

Dim strCriteria As String, vScore As Variant

strCriteria = "[start]=" & [Resid] & " And [HH_Size]=" & [HHS]
Debug.Print strCriteria    ' Check the Immediate Window (Ctrl+G) to see output
vScore = DLookUp("Score", "AFF", strCriteria)   ' Use the criteria string in your function

 

[jeffatwork]

DLookUp("Score","AFF","[start]=" & [Resid] And "[HH_Size]=" & [HHS])
                                               ^
                                               |
                                    This needs to move to here:
                                           |
                                           v
DLookUp("Score","AFF","[start]=" & [Resid] " And [HH_Size]=" & [HHS])
                                           ^
                                           |
                                You also need another '&':
                                           |
                                           v
DLookUp("Score","AFF","[start]=" & [Resid] & " And [HH_Size]=" & [HHS])

Thanks Cheeky - this works perfect.

 

[cheekybuddha]

No problem!

It was @Gasman who did all the work!

 

[jeffatwork]

You have " the wrong side of AND and also missing an & to continue the concatenation.
Put criteria into a string variable and debug.print until correct, then use that in the function.

You have " the wrong side of AND and also missing an & to continue the concatenation.
Put criteria into a string variable and debug.print until correct, then use that in the function.

Tha

You have " the wrong side of AND and also missing an & to continue the concatenation.
Put criteria into a string variable and debug.print until correct, then use that in the function.

Thanks Gasman!