Function Problems

How to Find the Closest Invoice Date with DMin and DMax in Microsoft Access

Algirdas Jasaitis Oct 20, 2026 868 views

Question details

The user needs to construct a Microsoft Access query that finds the closest invoice date relative to a given StartDate, falling back to a previous date if a future one doesn't exist.

How to Find the Closest Invoice Date with DMin and DMax in Microsoft Access

Product: Microsoft Access
Device & OS: not provided
Scenario: Creating a database query to find the nearest chronological record relative to a specific target date across related tables.
Observed behavior: The goal is to successfully return the first invoice date on or after StartDate, and if none exists, return the latest invoice date on or before StartDate, while avoiding #Error results.

Before you start

Ensure you have your Access database open in Design View and verify the exact field names for your invoice date, start date, and matching keys across your tables.

Solution 1Recommended

Use Nz with DMin and DMax to Find the Closest Date

Utilize the Nz function to fall back smoothly from DMin to DMax if the initial criteria return Null, preventing expression errors.

Because the DMin and DMax domain functions return a Null value rather than an error when no records match your criteria, they can be safely wrapped in an Nz function. This avoids the need for complex error handling or type conversions like CDate.

Open Query Design

Right-click your query in the Navigation Pane and select Design View.

Select the Target Field

Click into an empty column in the query design grid where you want the calculated date to appear.

Enter the Nz Expression

Type the following exact syntax into the field: Nz(DMin("[Invoice Date]", "SOLD_TABLE", "[Invoice Date]>=#" & [StartDate] & "# AND [KEY]='" & ANALYSIS.[KEY] & "'"), DMax("[Invoice Date]", "SOLD_TABLE", "[Invoice Date]<=#" & [StartDate] & "# AND [KEY]='" & ANALYSIS.[KEY] & "'"))

Run the Query

Click the Run button (!) in the Ribbon to execute the query and verify that it fetches the nearest invoice date correctly.

Use Nz with DMin and DMax to Find the Closest Date

Domain Functions Logic: By placing DMin as the first argument and DMax as the second argument of Nz, Access will automatically evaluate DMax only if DMin finds no future dates.

Solution 2

Create a VBA User-Defined Function (UDF)

If inline expressions become too complicated to read or maintain, offload the logic into a custom VBA function.

Free Microsoft Office alternative

Looking for a Lightweight Office Suite?

While WPS Office does not include a relational database management tool like Microsoft Access, it is an excellent, free alternative for your everyday document, spreadsheet, and presentation needs. Export your Access data to Excel and seamlessly analyze it using WPS Spreadsheets.

1. Download WPS Office: Visit the official WPS website to download and install the free suite.
2. Export Access Data: In Microsoft Access, export your query results to an Excel workbook (.xlsx).
3. Analyze in WPS Spreadsheets: Open the exported file in WPS Spreadsheets to utilize advanced formulas, pivot tables, and charting tools seamlessly.

Fully compatible with Microsoft Excel (.xlsx), Word (.docx), and PowerPoint (.pptx) formats.Free and lightweight, taking up minimal system resources.Powerful data analysis features in WPS Spreadsheets to handle exported database records.Familiar, tabbed user interface makes transition and multitasking intuitive.

microsoft office alternative - wps office

Frequently Asked Questions

Why does my Microsoft Access query return #Error instead of a date?

The #Error result typically occurs when a function encounters an invalid data type, attempts an impossible calculation, or fails to properly handle Null values. Wrapping functions in Nz or adding explicit error handling often resolves this.

Can I use IIf instead of Nz to handle Null values from DMin?

Yes, you can use the syntax IIf(IsNull(DMin(...)), DMax(...), DMin(...)). However, the Nz function is designed specifically for this scenario and provides a much shorter and cleaner expression.

Why do I need to enclose dates in hashtags (#) in Access queries?

Microsoft Access requires literal date values inside SQL strings or domain functions to be enclosed in hash/pound signs (#). This tells the Access database engine to treat the string as a Date/Time data type rather than generic text.

Excel & Data Problems