DLookup criteria help ...

DKoehne

Registered User.
Local time
Yesterday, 18:16
Joined
Apr 10, 2017
Messages
49
Hello.



I have the following dlookup scenario:


=Nz(DLookUp("[DocNumber]","tblDocuments","[tblClients_fldDRCClientID]=" & [Forms]![frmScreen_SubformTestingPB]![MeFKID] & "And [RelatedDebtAcctNo] = [Forms]![frmScreen_SubformTestingPB]![txtAcctNumber]" & "And [tblSubject_SubjectCode] In ('1', '4', '5')"),0)


-----------------------------


It works fine without the last criteria

& "And [tblSubject_SubjectCode] In ('1', '4', '5')"),0)
Where am I going wrong in this part of the code?
tblSubject_SubjectCode field is integer data type

Thanks.
 
Hi,

If it’s integer data type, then you could try In(1,2,3).

Hope it helps...
 
It works fine without the last criteria

Are you sure? You made the same error on the 2nd criteria as you did the 3rd:

... & "And...

You need a space between the And and the quote mark. Otherwise And gets added to whatever is in the variable:

"[tblClients_fldDRCClientID]=123And [RelatedDebtAcctNo]...
 
The combo of both solutions worked. Thanks!


Here is the final for those who may want it in the future...




=Nz(DLookUp("[DocNumber]","tblDocuments","[tblClients_fldDRCClientID]=" & [Forms]![frmScreen_SubformTestingPB]![MeFKID] & " And [RelatedDebtAcctNo] = [Forms]![frmScreen_SubformTestingPB]![txtAcctNumber]" & " And [tblSubject_SubjectCode] In (1,4,5)"),0)
 
Hi,

Congratulations! Glad to hear you got it sorted out. Good luck with your project.
 

Users who are viewing this thread

Back
Top Bottom