Dlookup with with 2 fields as criteria (1 Viewer)

[xveganx007]

i am trying to get the apgno stored in my variable str1 using the values from the feilds AUDIT_NO & INDXITMNO. but i don't know the syntax for doing this. this is what i have so far:
strAudit = [AUDIT_NO]
strIndex = [INDXITMNO]
str1 = DLookup("[APGNO]", "QASALPRA", "[AUDIT_NO] = " & strAudit & "[INDXITMNO] = " & strIndex)

how do i fix this?
thanks matt

 

[Pat Hartman]

Two things.
1. you're missing the "AND" between the two conditions.
2. If the variables are text, they need to be surrounded by quotes. I'll put single quotes in my restated expression and they will work unless the field itself contains single quotes (as in O'Brian).

If both AUDIT_NO and INDXITMNO are defined as numeric, use the following:
str1 = DLookup("[APGNO]", "QASALPRA", "[AUDIT_NO] = " & strAudit & " AND [INDXITMNO] = " & strIndex)

If they are text, use the following:
str1 = DLookup("[APGNO]", "QASALPRA", "[AUDIT_NO] = '" & strAudit & "' AND [INDXITMNO] = '" & strIndex & "'")

 

[xveganx007]

Thanks Pat, that did the trick!