Others

Retrieve the Second-Most-Recent Date in a Microsoft Access Query

Chanuka GeekiyanageChanuka Geekiyanage Nov 22, 2026 868 views

Question details

Find the second-most-recent distinct transaction date in a Microsoft Access query.

Retrieve the Second-Most-Recent 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.
Before you start

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.

Solution 1Recommended

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.

1
Create the top-two query

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;

2
Save the query

Save the query as qryTwoLatestDates, then run it to check that it returns the two most recent distinct non-null dates.

3
Return the second-most-recent date

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.

4
Use the result in another query

Save the second query, for example as qrySecondLatestDate. Add it to another query and compare its SecondMostRecentDate field with the transaction date field.

Find the two latest distinct dates with saved Access queries
Fewer than two dates: The HAVING clause returns no row when fewer than two distinct non-null dates are available, so the latest date is not mistaken for the second-most-recent date.
Free Microsoft Office alternative

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.

Sort and review transaction dates in WPS SpreadsheetCompatible with Microsoft Excel formats, including XLSX and XLSFamiliar spreadsheet interface for an easier transitionFree and lightweight office tools
QA img-9

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.