Only records with the latest date

[mloioza]

Hi. I'm sure this is simple, but I'm just stuck.

How do I get a query to output from a dataset just the records which have the latest date as per a Date field?

I've tried using a max/first/last in the totals row, but this does not produce the desired result - for example, if a particular product was sold a year ago, the product will show up with this date, as this is the latest date that this product was sold BUT I only want products sold on the latest date, say yesterday.

Importantly, I don't want the user to have to look at the data and identify what the latest date is - (otherwise could just use criteria) - I want the query to identify the most recent date in the dataset by looking at the date field and then just produce records with that date.

Any help appreciated! (Using Access 2007)

Thanks.

 

[plog]

SELECT YourTableName.*
FROM YourTableName
WHERE YourTableName.[YourDateField] = (SELECT Max(YourTableName.[YourDateField]) AS [LatestDate] FROM YourTableName);

Replace YourTableName with your table's name, and YourDateField with your date field's name.

 

[mloioza]

Wow! Thanks for the very very quick reply. It works!! Thanks!!