Returning Query Record with Highest/Latest Date

[music_al]

I have a query that lists the status of an order and the date that the status was reached.

When I use the MAX in Group By on the Date field it is still returning all the records.

ID Status Date
1 Received 1/5/2018
2 Processed 3/5/2018
3 Shipped 7/5/2018

I then want to pass the STATUS to a text control on a form.

Any help would be appreciated.

 

[plog]

Kind of an ambigous post, you haven't fully told us what the issue is, nor what you are trying to accomplish.

If you want a form to display a value, you might try DLookup, or possibly DMax.

If you want help with a query, then post SQL and tell us how it is not generating what you want.

My guess is you don't understand what GROUP BY does. It returns unique permutations of values in your database for everything not put into a function in the SELECT. That means every result you see is unique, if its "not unique enough" then you probably need to remove fields from the SELECT and GROUP BY

 

[arnelgp]

=dlookup("status", "yourTable", "[date]=#" & format(dmax("[date]","yourTable"),"mm/dd/yyyy") & "#")

 

[Minty]

I guess that isn't all the fields form your OrderStatus table. You need to get the Max([Date]) grouped by OrderID , then link the ID of that record back to your order table.