General discussion

Locked

Access 97 DMax Function Question

By learneverything ·
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.

This conversation is currently closed to new comments.

5 total posts (Page 1 of 1)  
| Thread display: Collapse - | Expand +

All Comments

Collapse -

Access 97 DMax Function Question

by mbarker734 In reply to Access 97 DMax Function Q ...

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

Collapse -

Access 97 DMax Function Question

by learneverything In reply to Access 97 DMax Function Q ...

Thanks for your help, Mark.

Collapse -

Access 97 DMax Function Question

by rraheb In reply to Access 97 DMax Function Q ...

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.

Collapse -

Access 97 DMax Function Question

by learneverything In reply to Access 97 DMax Function Q ...

Thanks for your help, Rraheb. I'm afraid I needed some clarification to understand what I needed to change.

Collapse -

Access 97 DMax Function Question

by learneverything In reply to Access 97 DMax Function Q ...

This question was closed by the author

Back to Software Forum
5 total posts (Page 1 of 1)  

Related Discussions

Related Forums