MS Access DLOOKUP with text and nested DLOOKUP for criteria

Question

I have a combobox on a form that contains search terms. The user chooses a search term and this looks up to a table containing the number X. The RVU (a number) of X is looked up in another table given the category is equal to the string 'PHYS'. I was using nested DLOOKUP statements to look up the number X and then use that number X and the string criteria to look up the RVU. Here's my code:

FH_array(0) = Val(Nz(DLookup("[RVU]", "[FORES IP Picker]", "[IP]= " & Val(Nz(DLookup("[FORES]", "[IP Number Xwalk]", "[Reference Name] = '" & Me.Ref_Name & "'"), 0))), ""))

I wasn't having luck so I broke it down to debug:

a = Val(Nz(DLookup("[FORES]", "[IP Number Xwalk]", "[Reference Name] = '" & Me.Ref_Name & "'"), 0))
Debug.Print "a:"; a 'returns value 279
aa = Val(nz(DLookup("[RVU]", "[FORES IP Picker]", "[IP] = " & a & " and [Cost Category] = 'PHYS')))
Debug.Print "aa:"; aa

I'm getting a syntax error on the line for variable aa. if I changed the code from

aa = DLookup("[RVU]", "[FORES IP Picker]", "[IP] = " & a & " and [Cost Category] = 'PHYS')

to

aa = DLookup("[RVU]", "[FORES IP Picker]", "[Cost Category] = 'PHYS'" And "[IP] = " & a)

I get a run-time error 13 type mismatch

All the variables are declared as variant and called properly. The array FH_array is sized correctly. I copied this code from another database that does the same type of nested DLOOKUP but it has only one criteria and therefore works. I can't figure out what syntax I'm missing or where the type mismatch is to get it to work.

@HansUp The result of the first is [IP] = 279 and [Cost Category] = 'PHYS' and the result of the second is error type 13 — Access_Query

HansUp HansUp · Accepted Answer · 2014-10-27T17:37:04

You need a single valid string as the DLookup criteria option. Use the Immediate window to examine what you have for the criteria in the final DLookup example.

Debug.Print "[Cost Category] = 'PHYS'" And "[IP] = " & a

That is actually a "logical conjunction" of two strings. (That will make more sense if you review the Access help topic for the And Operator.)

And since you're using And with two string expressions, Access complains about type mismatch, the same as it does with this simpler example:

Debug.Print "a" And "b"

So you need to create a single valid string for the criteria option ...

a = 279
Debug.Print "[Cost Category] = 'PHYS' And [IP] = " & a
[Cost Category] = 'PHYS' And [IP] = 279

Translating that back to the last DLookup in your question ...

Dim strCriteria As String
strCriteria = "[Cost Category] = 'PHYS' And [IP] = " & a
Debug.Print strCriteria ' <- just to make sure you got what you need
aa = DLookup("[RVU]", "[FORES IP Picker]", strCriteria)

MS Access DLOOKUP with text and nested DLOOKUP for criteria

1 Answers