Retrieve the Second-Most-Recent Date in a Microsoft Access Query
Question details
Find the second-most-recent distinct transaction date in a Microsoft Access query.

- Product
- Microsoft Access
- Device & OS
- not provided
- Scenario
- Compare the two latest distinct transaction dates or use the second-most-recent date as a criterion in another query.
- Observed behavior
- The desired result is the earlier of the two latest distinct non-null dates.
Identify the table and date field you want to query, and confirm that the field uses a Date/Time data type. Replace tblDate and Date1 in the examples with your own names.
Find the two latest distinct dates with saved Access queries
Use a saved query to select the two latest distinct dates, then return the earlier one with Min.
In Access, select Create > Query Design, switch to SQL View, and enter: SELECT DISTINCT TOP 2 Date1 FROM tblDate WHERE Date1 Is Not Null ORDER BY Date1 DESC;
Save the query as qryTwoLatestDates, then run it to check that it returns the two most recent distinct non-null dates.
Create another query in SQL View and enter: SELECT Min(Date1) AS SecondMostRecentDate FROM qryTwoLatestDates HAVING Count(*) = 2; Run the query to return the earlier of the two dates.
Save the second query, for example as qrySecondLatestDate. Add it to another query and compare its SecondMostRecentDate field with the transaction date field.

Review Transaction Dates in WPS Spreadsheet
WPS Office is a free, lightweight, easy-to-use Microsoft Office alternative with a familiar interface. Open compatible spreadsheet files to review transaction dates and migrate spreadsheet work easily; it does not run or fix Microsoft Access queries.

Frequently Asked Questions
Why does the query use DISTINCT?
DISTINCT ensures that repeated transactions on the same date count as one date. Without it, the two returned rows could have the same latest date.
How can I return both the latest and second-most-recent dates?
After saving qryTwoLatestDates, create a query in SQL View with: SELECT Min(Date1) AS SecondMostRecentDate, Max(Date1) AS MostRecentDate FROM qryTwoLatestDates HAVING Count(*) = 2;
What happens if there is only one distinct transaction date?
The aggregate query with HAVING Count(*) = 2 returns no row. Check the source table for at least two distinct non-null dates.
Can I use DMin to retrieve the second-most-recent date?
Yes. After confirming that qryTwoLatestDates contains two rows, use DMin("Date1", "qryTwoLatestDates") in an expression. If the query contains only one date, DMin returns that date instead.




