Add an Optional Date Range to a Microsoft Access Search Form
Question details
Use optional start and end date controls on an Access form to filter query records.

- 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.
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.
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.
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.
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.
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.

Include null dates and the full end date
Use explicit blank-control checks when all records, including those with null dates, must appear if both controls are blank.
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.

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.




