Get Recent Date

[Parra]

I want to create a query that will give me only the data for the most current date in the table

So if I have a table with the following dates:
1/1/03
1/1/03
1/9/03
2/2/03
2/10/03
The query will automatically choose 2/10/03.

Any suggestions?

 

[LarryJ]

Could you simply create a query that sorts the records descending by date, then set the query properties to return only 1 record?

This would be the record with the most recent date.

Now if you had several records with that date, you'd still only get the one, but it's a start.

If you need all records with the most recent date. Build another query and use the date from this query to find all the records with that date.

 

[Parra]

There are many records that have the most current date I am looking for.

What I need is a expression to enter into the criteria. Something like:

=Date(most current)
=Date(top)

I need something, that will extract the newest date in the field.

Thanks

 

[LarryJ]

Create the first query to find the most recent date:

qryMostRecent
SELECT DISTINCT TOP 1 Table1.PurchaseDate
FROM Table1
ORDER BY Table1.PurchaseDate DESC;


Create another query to join on that date:

qryAllRecordsWithMostRecent
SELECT Table1.*
FROM Table1 INNER JOIN qryMostRecent ON Table1.PurchaseDate=qryMostRecent.PurchaseDate;



When you execute the second query, only those records with the most recent date will be shown.

 

[Parra]

:D Thanks Your suggestion worked.

Parra