Dlookup issue

[punderful]

I am still trying to use DLookup to pull a specific record from a detail table for a project. Here's the contents of my table:

Site# Task NTPDate DueDate DoneDate BillDate
A1 NIER 1/1/02
A1 Noise 1/2/02
A2 NIER 2/1/02
A2 Noise 2/2/02
A3 Nier 3/1/02
A3 Noise 3/2/02

My expression:

NierBid: DLookUp("[dtmNTPDate]","tblDocumentDetail","[txtTask]='Noise'&quot

What is happening is that the query is finding the 1st record with "noise" and using that date for each site# : 1/2/02. If I change the task to "NIER" it finds the 1st record with a NIER and fills each record with "1/1/02".

The results (query) I need is:

Site# NierNTP NoiseNTP NierDue Noise Due
A1 1/1/02 1/2/02
A2 2/1/02 2/2/02
A3 3/1/02 3/2/02

Can someone help me revise my DLookup so that the results produce the NTPDate for the specified task FOR THAT SITE#?
Any help is appreciated!!

 

[MikePP]

Hi

The DLookup Command is used to find a single database value.

To get a range of values, you need to use an alternative technique. (e.g. an ADO recordset)

Regards

Sadcow

 

[punderful]

Can I create a multiple recordsets in the SQL section of the query, or should I create a function that will create the recordset?