Dlookup multi criteria (1 Viewer)

[DaManJC]

Access World,

I'm currently in a bind with Dlookup feature in MS access. I have tried in many different formats mentioned in various forums and websites but i just have not been able to get the "AND" function to add second criteria. Any help is very much appreciated.

=DLookUp("[tbl_BackLogWithLinkedWO]![WORK_ID]","[tbl_BackLogWithLinkedWO]","[tbl_BackLogWithLinkedWO]![SO No]= '" & [SO No] & "' and [tbl_BackLogWithLinkedWO]![LINE_NO]= '" & [LINE_NO] & "'")

 

[theDBguy]

Hi. Welcome to AWF!

That looks okay to me. What exactly were you trying to accomplish with it?

 

[Gasman]

Put the criteria into a string variable and Debug.Print that variable.
That will show you your errors, also you can post the result here within code tags if you need more help.

 

[namliam]

Three likely problems... well two actually
"[tbl_BackLogWithLinkedWO]![WORK_ID]" > "[WORK_ID]" , no need to prefix the table name... neither so in the where cluase.

And NO suggest a number, which doesnt require quotes in the string

DLookups are usually a bad habit though, consider your considerations about using it at all.

 

[Minty]

You don't need all those table references and square brackets - I tend to leave them on the criteria fields just to make it obvious they are field criteria.

=DLookUp("WORK_ID","tbl_BackLogWithLinkedWO","[SO No]= '" & [SO No] & "' and [LINE_NO]= '" & [LINE_NO] & "'")

So what error are you getting, and is SO_No a text field or number and is Line_No a text field or a number?

 

[DaManJC]

Hi. Welcome to AWF!

That looks okay to me. What exactly were you trying to accomplish with it?

I'm trying to get the actual Work_ID linked to the SO No and Line_No,

 

[theDBguy]

I'm trying to get the actual Work_ID linked to the SO No and Line_No,

Hi. Looks like [SO NO] is Text but [LINE_NO] is a Number. Were you getting a #Type! as a result?

 

[DaManJC]

Hi. Looks like [SO NO] is Text but [LINE_NO] is a Number. Were you getting a #Type! as a result?

#error is what I'm getting once I add the second criteria

 

[Minty]

You are comparing them both as text, but Line_No is a number so don't add the ' ' around it;

=DLookUp("WORK_ID","tbl_BackLogWithLinkedWO","[SO No]= '" & [SO No] & "' and [LINE_NO]= " & [LINE_NO] )

 

[theDBguy]

#error is what I'm getting once I add the second criteria

Okay, it's not what I was expecting, but let's try it anyway. What do you get with this?

 =DLookUp("[WORK_ID]","tbl_BackLogWithLinkedWO","[SO No]= '" & [SO No] & "' AND [LINE_NO]= " & [LINE_NO])

 

[DaManJC]

It worked!. I really appreciate your input, I've been on this on and off for 2 days. thank you so much along with everyone else that replied.

 

[theDBguy]

It worked!. I really appreciate your input, I've been on this on and off for 2 days. thank you so much along with everyone else that replied.

Hi. Glad we could assist. Good luck with your project.