How to use DLookup to get last date value.

[reginaldmerritt]

I'm trying to see if I can use DLookup instead of a Recordset to find a specific record.

To find this record I think I only need one criteria. The problem is that this record must be the record with the oldest date amongst all the records with this criteria.

DLookup("Bottom 1 EndDate", "TBTasks", "[ProjectID] = " & Me.ProjectID)
 

The expression part of the Dlookup code "Bottom 1 EndDate" obviously needs tweaking or is that even possible?

I guess I could create a RecordSet and then loop though the RecordSet picking out records where the "[ProjectID] = " & Me.ProjectID and then storing the EndDate into an array; which I could then use to compare each array entry to each other. But this seems like the Long Way Round to me.

Is this the best way of going about this or is there a better way?

Any Ideas Welcome, Thanks.

[liimra]

Just Use:
DMin("Bottom 1 EndDate", "TBTasks", "[ProjectID] = " & Me.ProjectID)
to get the Oldest Date


And

DMax(("Bottom 1 EndDate", "TBTasks", "[ProjectID] = " & Me.ProjectID)
to get the Newest Date.

Regards,
Ali

[liimra]

Forgot to add,

Use:

DLast("Bottom 1 EndDate", "TBTasks", "[ProjectID] = " & Me.ProjectID) 

to get the last entered date irrespective of its position among others

Please note that it is recommended not to use space for names. Field Name: "Bottom 1 EndDate" --> "Bottom1EndDate"

Regards,
Ali

[reginaldmerritt]

Thanks Ali. I knew there had to be a method I could use. Works perfectly thanks.

BTW The field is named 'EndDate' , was trying to use 'Bottom 1' as you would with 'Top 1' in an SQL statement.

Many Thanks

[liimra]

Glad it worked,

Regards,
Ali