Need help with DLoopup syntax

[accessrookie2]

Someone helped me yesturday put together this code that works:

DLookup([partColumnNumber], "ExcelTable", [zColumnNumber] & " = '" & zNumber & "'")

all the variables are strings if that matters. I need to add another condition in the criteria. I want it to return the result where the zcolumnNumber = zNumber And the topBottomColumnNumber = zLetter

I've tried this code below but it gives me a data mismatch. I'm sure I have a quote in the wrong place but I cannot figure it out, I've either gotton an error or it returns a null value (when there is a match in the table it should be returning)

DLookup([partColumnNumber], "ExcelTable", [zColumnNumber] & " = '" & zNumber & "'" And [topBottomColumnNumber] & " = '" & zLetter & "'")

If anyone can tell me what I need to fix it would be much appreciated, thanks.

 

[RuralGuy]

ALL the arguments of the Domain functions are strings. See if this link helps.

 

[accessrookie2]

I'm getting confused with all the quotation makes and apostrophes. I have the second half of the criteria set up exactly the same as the first one. Do I have to combine them into the same string to make them work?

Would you be able to help me do that from my code above?

 

[RuralGuy]

Try this:
DLookup([partColumnNumber], _
"ExcelTable", _
"[zColumnNumber] = '" & zNumber & "' And [topBottomColumnNumber] = '" & zLetter & "'")

 

[accessrookie2]

It says run-time error '2001':

You canceled the previous operation

I'm not sure what else to try, if it helps the values are:
zColumnNumber = "F3"
zNumber = "35"
topBottomColumnNumber = "F5"
zLetter = "T"

EDIT:

This code seems to work:
DLookup([partColumnNumber], "ExcelTable", [zColumnNumber] & " = '" & zNumber & "' And " & [topBottomColumnNumber] & " = '" & zLetter & "'")

Looks like I needed the column numbers out of the quotes. Thanks for pointing me in the right direction!

 

[RuralGuy]

I think your first argument needs to be a string in spite of your first post in this thread.
DLookup("[partColumnNumber]", _
"ExcelTable", _
"[zColumnNumber] = '" & zNumber & "' And [topBottomColumnNumber] = '" & zLetter & "'")