DLookup criteria help ...

[DKoehne]

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]

Hi,

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

Hope it helps...

 

[plog]

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]

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]

Hi,

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