Go Back   Access World Forums > Microsoft Access Discussion > Queries

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 11-16-2018, 05:30 PM   #1
DKoehne
Newly Registered User
 
Join Date: Apr 2017
Posts: 31
Thanks: 4
Thanked 0 Times in 0 Posts
DKoehne is on a distinguished road
DLookup criteria help ...

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.

DKoehne is offline   Reply With Quote
Old 11-16-2018, 05:35 PM   #2
theDBguy
Newly Registered User
 
theDBguy's Avatar
 
Join Date: Oct 2018
Posts: 222
Thanks: 3
Thanked 36 Times in 35 Posts
theDBguy is on a distinguished road
Re: DLookup criteria help ...

Hi,

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

Hope it helps...
__________________
Just my 2 cents...

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
theDBguy is offline   Reply With Quote
Old 11-16-2018, 08:43 PM   #3
plog
AWF VIP
 
Join Date: May 2011
Posts: 8,996
Thanks: 10
Thanked 2,160 Times in 2,115 Posts
plog is a jewel in the rough plog is a jewel in the rough plog is a jewel in the rough
Re: DLookup criteria help ...

Quote:
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]...

plog is offline   Reply With Quote
Old 11-19-2018, 07:03 AM   #4
DKoehne
Newly Registered User
 
Join Date: Apr 2017
Posts: 31
Thanks: 4
Thanked 0 Times in 0 Posts
DKoehne is on a distinguished road
Re: DLookup criteria help ...

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)
DKoehne is offline   Reply With Quote
Old 11-19-2018, 08:50 AM   #5
theDBguy
Newly Registered User
 
theDBguy's Avatar
 
Join Date: Oct 2018
Posts: 222
Thanks: 3
Thanked 36 Times in 35 Posts
theDBguy is on a distinguished road
Re: DLookup criteria help ...

Hi,

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

__________________
Just my 2 cents...

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
theDBguy is offline   Reply With Quote
Reply

Tags
dlookup

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
DLookUp with 2 criteria connerlowen Modules & VBA 3 08-05-2015 06:42 AM
Dlookup criteria melika Modules & VBA 2 11-08-2007 10:37 AM
DLookup criteria paul25_uk Forms 4 09-14-2004 12:15 AM
DLookup with 2 criteria. hooi General 5 10-22-2003 01:01 AM




All times are GMT -8. The time now is 07:26 PM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2018, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World