Date search based on a parameter???

[tsm15]

I would like to be able to search for the last date that was entered into a date field based on a parameter.

I have a form that displays info about a piece of machinery and a subform that displays the service record. But, I would like to pull the last date from this subform (table) so that I could display something like "This equipment was last serviced on (date)."

How would I do this, it is probably easy but I am stumped.

thanks
tom

 

[RickSpr]

If by "last date" you always mean the date in the very last row in the subform's record source, you can use the SQL Last() function to get it. In an SQL statement it looks like this:

    SELECT Last(ServiceDate) FROM MyTable WHERE <selecting> ORDER BY <sorting>;

In a query grid, add the tables, drop the date field into the grid along with any other fields you need for selecting, and then choose View>Totals from the menu. This reveals a Total: row in the grid. It has a combo box that lists all the aggregate (i.e., summarizing) functions available. Choose "Last" from the list in the column for your date field. You probably want to uncheck the Show: check box for all the other fields.

Keep in mind that Last() gets the last record from the selected rows in the order they are sorted, so you want to be sure that your ORDER BY or Sort: row in the query grid matches the sorting in your subform's record source. Rick Sprague