Find record with current date

[MaggieLeatherman]

Hi, what is the most efficent way to look thru a table and locate the record with the most recent "ArchivedDate" field. I need to grab the "ProjNumb" from that record. Currently I'm looping thru the table comparing dates and my logic isn't working and it takes a long time.
Thanks, Maggie

 

[Remou]

How about:
[tt]DLookup("ProjNo", "tblProjects", "ProjectDate=#" & DMax("ProjectDate", "tblProjects") & "#")[/tt]

 

[JPMontreal]

With a query that look like that:

Select top 1 Projnumb, ArchivedDate
From MyTable
Order by ArchivedDate DESC, Projnumb DESC

Top 1 will give you one record, since the date is descending you will have the latest one, the project number is descending as well in case that more than one project is created the same day


Jean-Paul
Montreal
To send me E-Mail, remove “USELESSCODE”.
jp@USELESSCODEsolutionsvba.com