DlookUp In Query

[shamas21]

Hi All

Im trying to do a DlookUp in a query.

My Query has 2 fields

Field1: CaseID (Bring back the CaseID fom another query)

Field2: DLookUp("[Date]","tblPayments","[CaseID]= [qry1InvoicedPayments].[CaseID]")

...So my question is that my DlookUp doesnt work as it doesnt recognise [qry1InvoicedPayments].[CaseID] within my current query to bring back the [Date] for the specified row with the CaseID in it.

i.e.

It looks like

CaseID | Date

12623
63423
57434
85653

Any Help is greatly Appreciated

Thanks

 

[MarkK]

Maybe you need to evaluate the field reference outside the string...

"[CaseID] = " & [qry1InvoicedPayments].[CaseID]

 

[Galaxiom]

Unless I misunderstand what you are trying to do, don't use the DLookUp at all.
Use a join between [qry1InvoicedPayments] and [tblPayments] on [CaseID].

It will be many times faster.

 

[shamas21]

Maybe you need to evaluate the field reference outside the string...

"[CaseID] = " & [qry1InvoicedPayments].[CaseID]

Thanks that worked great!