DLookup help (1 Viewer)

DKoehne

Registered User.
Local time
Today, 13:21
Joined
Apr 10, 2017
Messages
49
Hello. The Dlookup below works but does not seem to distinguish between the (1,4,5) [tblSubject_SubjectCode] criteria. Do I have the syntax right for this?

I am looking for any of the three - it could be literally a 1 or 4 or 5 in that field.

Any help with this would be appreciated ...




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

isladogs

MVP / VIP
Local time
Today, 20:21
Joined
Jan 14, 2017
Messages
18,186
Your syntax is wrong on the second part of the filter.
Try this - untested

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

NOTE: The colours aren't relevant - I'm just testing the PHP code button
 

DKoehne

Registered User.
Local time
Today, 13:21
Joined
Apr 10, 2017
Messages
49
It doesn't like that. It seems to wants to treat the underscores as "invalid characters." It does work on one of the choices but does not work on all of them so, to me, it isn't recognizing the other choices. Any other thoughts?
 

isladogs

MVP / VIP
Local time
Today, 20:21
Joined
Jan 14, 2017
Messages
18,186
Looking at it again, I'm really not surprised....

Is [tblClients_fldDRCClientID] a field name or meant to be field fldDRCClientID in table tblClients?
Similarly is [tblSubject_SubjectCode] meant to be field SubjectCode in table tblSubject?

What are the datatypes of each field used in the filter criteria?
 

DKoehne

Registered User.
Local time
Today, 13:21
Joined
Apr 10, 2017
Messages
49
Here is what I am doing and these are all for one table- tbldocuments:



I am going to a documents table and looking up a match by three variables. 1) The record by client number (foreign key = [tblClients_fldDRCClientID] then 2) [RelatedDebtAcctNo] that matches what's on the screen and I want to see if the value in 3) [tblSubject_SubjectCode] is entered as (1 or 4 or 5). I am returning the "DocNumber" in the function to a field on the screen and that doc number tells me "yes" a record for this transaction matching that criteria exists - so I can proceed. Without a 1,4 or 5 i am not allowed to proceed.



I am looking for correct "OR" syntax for "[tblSubject_SubjectCode] In (1,4,5)"
What's in Red above.
 

isladogs

MVP / VIP
Local time
Today, 20:21
Joined
Jan 14, 2017
Messages
18,186
Please could you first answer my questions from the previous post
 

George21

Registered User.
Local time
Today, 22:21
Joined
Jan 10, 2011
Messages
27
It doesn't like that. It seems to wants to treat the underscores as "invalid characters." It does work on one of the choices but does not work on all of them so, to me, it isn't recognizing the other choices. Any other thoughts?
Try Isladlogs corrections without underscores. He used them because he changed lines.

Sent from my SM-T825 using Tapatalk
 

isladogs

MVP / VIP
Local time
Today, 20:21
Joined
Jan 14, 2017
Messages
18,186
Ah....those underscores! Good point.

I was waiting (in vain) for the datatypes of each field - text/number etc
 

DKoehne

Registered User.
Local time
Today, 13:21
Joined
Apr 10, 2017
Messages
49
I have retested. It is working fine as is. I must have been inputting something wrong originally. Thanks for your inputs. :p
 

Users who are viewing this thread

Top Bottom