Multiple dates per ID - only show those whose LATEST date at least certain amount old

[creyc]

Intro:


I have a list of locations that need maintaining if the last known maintenance date is either 12H old, or has never been maintained (null date). When maintenance is performed on any location the date is added to a table, along with its location.

So far I have a simple database working fine, I can run a query and see all the locations and all their dates of last maintenance in one big list. By turning on "Totals" and setting the date field to "Last", I see only the latest maintenance date for each location, great so far.

Now I want to further filter this down to show only those whose LATEST dates are at least 12H old. I add this to the criteria in the form

DateDiff("h",[MaintDate],Date())>12

and of course, because I'm doing this wrong, get the error "Your query does not include the specified expression '....' as part of an aggregate function". I don't know what this error means, or how to achieve what I'm after. Do I need a second query to put my >12 hour criteria?

Thanks!

[June7]

Don't use DataDiff().

Try criteria under the MaintDate field:

<DateAdd("h",-12,Date())

Be sure to change the Total row to 'WHERE'.

[creyc]

Don't use DataDiff().

Try criteria under the MaintDate field:

<DateAdd("h",-12,Date())

Be sure to change the Total row to 'WHERE'.

Thank you this suggestion works wonderfully.

Yes, the date field does include time, as it's summed from [Date] + [Time]

Would it be better to use Date() or Now()?

[June7]

If you want 12 hours from the current hour then I suggest Now() instead of Date()