Software

Ask something
Discussion Locked

Access 97 DMax Function Question

0 helpful
5 replies

by learneverything

Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.

I have a query with both a date and time field. I want the results to return only the most recent for each projectID#. I think I need a DMax function, but it's giving me trouble.

I put the 2 date and time field together in a concatenated field (only a space between date and time) named "MgrDate&Time". Then I tried many variations of the following formula in it's own column, to no avail.

Sort Order: DMax("MgrDate&Time]","qry Manager Status")
--and --
Sort Order: DMax([MgrDate&Time],[qry Manager Status])
Any help would be appreciated. THANKS.

5 replies

by mbarker734

Sort your fields in your query (I call it qryProjectsort)in the following mannor:

ProjectID Accending

Date Decending

Time Decending

Then use the DFirst function to get the data. DFirst only returns the first record it finds that meets the criteria.

Assuming the fields of the form are ProjectID, Date, Time and the source table for the for is tblProjectDescription use the following line for each field you wish to use. Using ProjectId from tblProjectDescription as the key field.

=DFirst("Date","qryProjectsort","[ProjectID]=Form![ProjectID]")

=DFirst("Time","qryProjectsort","[ProjectID]=Form![ProjectID]")

and so on for each field in the form or report.

Hope that helps,

Mark

by rraheb

What you are doing is absolutely correct except for one small thing which is that you can not put this DMAX function in the sort settings.

What you need to do is that you need to select based on the DMAX, so that DMAX functions will be in the "Criteria" settings for the ProjectID# and then you can sort according to the ProjectID#.

Contact me for further help.

Rafik El-Raheb.

This discussion is closed to new replies.