469,281 Members | 2,484 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,281 developers. It's quick & easy.

How to use DLookup to get last date value.

reginaldmerritt
201 100+
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.

Expand|Select|Wrap|Line Numbers
  1. DLookup("Bottom 1 EndDate", "TBTasks", "[ProjectID] = " & Me.ProjectID)
  2.  
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.
Aug 21 '10 #1

✓ answered by 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

4 21189
liimra
119 100+
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
Aug 22 '10 #2
liimra
119 100+
Forgot to add,

Use:
Expand|Select|Wrap|Line Numbers
  1. 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
Aug 22 '10 #3
reginaldmerritt
201 100+
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
Aug 22 '10 #4
liimra
119 100+
Glad it worked,

Regards,
Ali
Aug 22 '10 #5

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

1 post views Thread by samotek | last post: by
reply views Thread by Lee Harr | last post: by
6 posts views Thread by Aussie Rules | last post: by
9 posts views Thread by ice | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by zhoujie | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.