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

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
Gordon_AtherleyAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
The SQL would look like this:

SELECT TOP 1 * FROM YourTable ORDER By YourDateField DESC

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
chaauCommented:
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 (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
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.
chaauCommented:
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
Gordon_AtherleyAuthor Commented:
Exactly the information I needed, thank you both.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.