We help IT Professionals succeed at work.

In MS Access 2013 query to find the record with the latest date

Gordon_Atherley
Gordon_Atherley asked
on
In MS Access 2013 I need a query to find the record with the latest date.

The record set has a date field. There will be a single record for a single date. Once identified, the record will be exported to XML.

For me, the novice, please advise on the coding of the query.

Gordon
Comment
Watch Question

Infotrakker Software
Most Valuable Expert 2012
Top Expert 2014
Commented:
The SQL would look like this:

SELECT TOP 1 * FROM YourTable ORDER By YourDateField DESC
Top Expert 2013
Commented:
You can use TOP 1 in the SQL query, like this:
SELECT TOP 1 dateColumn1, column2, column3
FROM aTable
WHERE  dateColumn1 <= [ParamDate]
ORDER BY dateColumn1 DESC

Open in new window

Please note that there is a "feature" in Access that under certain conditions it may return more rows than specified in the TOP clause, but it is unlikely if your dates are all different in the table
Scott McDaniel (EE MVE )Infotrakker Software
Most Valuable Expert 2012
Top Expert 2014
Commented:
Note to write this sort of query, you do this:

On the Ribbon, select to create a query in Design view
Do NOT add any tables
Click the View button and select "SQL View"
Enter the SQL shown above, making sure to use the correct Table and Field names.

Now switch over to Design view, and "run" the query.
Top Expert 2013
Commented:
BTW, if you prefer to do everything in the design view you can set the "Return" value to 1. This is equivalent of TOP 1. You can also specify the Sort property for the column. It is an equivalent of ORDER BYTOP 1 query

Author

Commented:
Exactly the information I needed, thank you both.