DLookup with 2 criteria (1 Viewer)

[CuriousGeo]

I have read many posts for this situation and have tried quite a few different suggestions, but it still won't work. I believe I am placing fields in the wrong order or I am missing the required double quotes or single quotes necessary for it to work. Could someone assist?

Background:

2 tables related by an intermediate table (tblBatch-BatchID)

tblOutgoingConsult has a foreign key (FKBatchID) related to tblBatch -BatchID

tblIncomingConsult has foreign key (FKBatchID) related to tblBatch -BatchID

I want to display the value on a Form from the field (OutgoingConsult) from tblOutgoingConsult, where the IncomingConsult matches the OutgoingConsult
The Form's source is the tblIncomingConsult, the original DLookup with 1 criteria WORKS:

=DLookUp("OutgoingConsult","tblOutgoingConsult","IncomingConsult=" & "OutgoingConsult")

i.e. you enter 123456 in the Incoming textbox, and if there is a match in the OutgoingConsult field, it shows in my Unbound textbox containing the DLookup definition.

Now, I want to add in the FKBatchID/FKBatchID from both tables must match as well as the 2nd criteria

How would I add that second criteria? I have tried multiple ways, but I believe I am getting the syntax wrong with placement of quotes….

 

[June7]

Would have to build a query object and reference that query as domain for DLookup.