Others

Add an Optional Date Range to a Microsoft Access Search Form

Maira MehtabMaira Mehtab Nov 22, 2026 868 views

Question details

Use optional start and end date controls on an Access form to filter query records.

Add an Optional Date Range to an Access Search Form
Product
Microsoft Access
Device & OS
not provided
Scenario
A search form supplies date boundaries to an Access query, but either or both boundaries may be left blank.
Observed behavior
The query should apply each entered boundary and return all records when both controls are blank.
Before you start

Confirm the names of the form, its two date controls, and the query's date field. Keep the form open when running the query so Access can read the control values.

Solution 1Recommended

Use Nz to make each date boundary optional

Use broad fallback dates when the query's date field contains dates without times and has no null values.

1
Open the query design

In Access, right-click the query in the Navigation Pane and select Design View. Add the date field to the design grid if it is not already present.

2
Enter the date criteria

In the date field's Criteria row, enter: >= Nz([Forms]![YourForm]![DateRangeStart], #1/1/100#) And <= Nz([Forms]![YourForm]![DateRangeEnd], #12/31/9999#). Replace the form and control names with your actual names.

3
Test the form

Open the search form, leave both date controls blank, and run the query. Then test with only a start date, only an end date, and both dates entered.

Use Nz to make each date boundary optional
Nulls and time values: These criteria exclude records whose date field is Null. If the field includes times, the end-date comparison includes only values up to the exact time entered; use the second solution to include the entire end date.
Free Microsoft Office alternative

Review Exported Access Data in WPS Spreadsheets

WPS Office is a free, lightweight Office alternative with a familiar interface and Microsoft Office format compatibility. Export Access records to a spreadsheet to review them in WPS Spreadsheets; WPS does not run or repair Access forms and queries.

Review exported date columns in WPS SpreadsheetsOpen and edit Microsoft Excel XLSX and XLS filesUse a familiar interface for a smoother move from Microsoft OfficeWork with a free, lightweight Office suite
microsoft office alternative - wps office

Frequently Asked Questions

Why does Access ask me to enter a parameter value?

Check the form and control names in the query criteria, such as [Forms]![YourForm]![DateRangeStart]. The form must be open when you run the query.

Can I enter only a start date or only an end date?

Yes. With either solution, leave the other form control blank and the query applies only the boundary you entered.

Why are some records missing when both controls are blank?

The Nz criteria exclude records with a Null date field. Use the explicit blank-control checks in the second solution if those records must also appear.

How do I include all times on the end date?

Use an exclusive upper limit of less than the day after the entered end date, as shown in the SQL View solution. Enter the end date without a time in the form control.