DLookup criteria help ... (1 Viewer)

DKoehne

Registered User.
Local time
Yesterday, 18:59
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.
 

theDBguy

I’m here to help
Staff member
Local time
Yesterday, 18:59
Joined
Oct 29, 2018
Messages
13,751
Hi,

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

Hope it helps...
 

plog

Banishment Pending
Local time
Yesterday, 20:59
Joined
May 11, 2011
Messages
10,262
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]...
 

DKoehne

Registered User.
Local time
Yesterday, 18:59
Joined
Apr 10, 2017
Messages
49
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)
 

theDBguy

I’m here to help
Staff member
Local time
Yesterday, 18:59
Joined
Oct 29, 2018
Messages
13,751
Hi,

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

Users who are viewing this thread

Top Bottom