Selecting the earliest record to appear on a report

[MISMonkey]

I have a summary project report that I am designing and on that report I want to display the next deliverable that each project has. I have managed to get this to work but in some instances it 'misses' returning a value. The only common thing I can see is that it misses returning a value for dates that begin with a zero (ie. 04/10/2010). Here is my formula;

=DLookUp("[Task / Deliverable]","Archive Project Deliverables","[Status]='Forecast' and [Project Name] = [PName] and [Forecast Date] =#" & Format(DMin("[Forecast Date]","Archive Project Deliverables","[Status]='Forecast' and [Project Name] = [PName]"),"dd/mm/yyyy") & "#")

This is just in a textbox on the report. I'm using the lookup to retrieve the descriptive content that I want displaying and then in the criteria I have embedded a DMin to select the smallest date in the subset of returned records for that project name (PName). I'm playing around with the date formatting cos I guess it's something to do with how the dates are interpreted but I'm going blind doing it!! any help appreciated.

I love deadlines. I like the whooshing sound they make as they fly by. (Douglas Adams)

 

[dhookom]

I'm surprised that this resolves:
[Project Name] = [PName]

I would expect to see something like:
... AND [Project Name] = """ & [PName] & """ And...

Duane
Hook'D on Access
MS Access MVP