Others

How to Filter Microsoft Access Data Using Unbound Date Controls

Amos Gikunda Oct 21, 2026 868 views

Question details

The user needs to filter records in a Microsoft Access form or query using unbound date input fields (From and To dates) while avoiding malformed SQL and date-formatting errors.

How to Filter Microsoft Access Data Using Unbound Date Controls

Product: Microsoft Access
Device & OS: not provided
Scenario: Setting up a custom date range filter on an Access form using unbound text boxes or date pickers to dynamically update the displayed query or form records.
Observed behavior: Improperly referenced unbound date controls or string concatenations often result in SQL syntax errors, ignored time values on the final day, or data type mismatch errors.

Before you start

Ensure you have added two unbound text boxes to your Access form (e.g., named 'StartDate' and 'EndDate') and set their Format property to a valid Short Date or General Date format so Access recognizes the inputs as dates.

Solution 1Recommended

Use a Parameterized Query with Inclusive/Exclusive Logic

This is the safest method as it handles missing (null) parameters automatically and accurately captures records that contain time values on the final day.

By relying on parameterized queries instead of VBA string concatenation, you avoid regional date format issues. Using a 'less than the end date plus one' logic ensures that any records logged with a specific time on the final day are not accidentally excluded.

Open the Query in Design View

Navigate to the Navigation Pane, right-click the query associated with your form, and select 'Design View'.

Enter the Date Criteria

In the Criteria row under your date column, enter the parameterized logic: WHERE (YourDateColumn >= [Forms]![YourFormName]![StartDate] OR [Forms]![YourFormName]![StartDate] IS NULL) AND (YourDateColumn < [Forms]![YourFormName]![EndDate] + 1 OR [Forms]![YourFormName]![EndDate] IS NULL).

Declare the Parameters

Click the 'Parameters' button in the Query Setup group on the Ribbon. Type the exact form references (e.g., [Forms]![YourFormName]![StartDate]) and set their Data Type to 'Date/Time'.

Requery the Form on Update

Open your form in Design View, select the unbound date controls, go to the Event tab in the Property Sheet, and add a simple macro or VBA code (Me.Requery) to the 'After Update' event.

Use a Parameterized Query with Inclusive/Exclusive Logic

Handling Time Values: Adding '+ 1' to the EndDate parameter and using the less-than operator (<) elegantly captures all records on the final day, right up to 11:59:59 PM.

Solution 2

Apply a Dynamic Form Filter using VBA

This method leaves the form's base RecordSource unrestricted and dynamically applies a filter string via VBA when the user inputs date ranges.

Free Microsoft Office alternative

Looking for a Lightweight Data Management Alternative? Try WPS Office

While WPS Office does not feature a relational database tool like Access, it offers a highly capable and completely free spreadsheet application. If your database needs primarily revolve around filtering lists, tracking dates, and generating reports, WPS Spreadsheets handles complex date logic and large datasets effortlessly while maintaining full compatibility with Microsoft Excel formats.

1. Open your data file: Launch WPS Spreadsheets and open your dataset containing the date columns you wish to filter.
2. Enable the Data Filter: Select the header row of your data, navigate to the 'Data' tab on the top ribbon, and click 'Filter'.
3. Apply a Custom Date Filter: Click the dropdown arrow on your date column, select 'Date Filters', and choose 'Between' to easily set custom Start and End dates.

Fully compatible with Microsoft Excel (.xlsx, .xls), Word, and PowerPoint file formats.Powerful built-in data filtering, conditional formatting, and PivotTable capabilities.Lightweight application with fast launch times and minimal system resource consumption.Free to use with a familiar, tabbed user interface for seamless workflow transitions.

microsoft office alternative - wps office

Frequently Asked Questions

Why do I get a 'Data type mismatch in criteria expression' error when filtering dates?

This usually occurs when the unbound form control is treated as standard text rather than a date, or if regional date formats are misunderstood by the SQL engine. Ensure the unbound control's Format property is set to a Date format, or explicitly format the values as 'mm/dd/yyyy' in your VBA code.

How do I make the 'From' or 'To' date parameters optional?

You can make them optional by incorporating 'OR [ControlName] IS NULL' in your SQL WHERE clause. This instructs the query to ignore the date parameter boundary if the user leaves the unbound text box blank.

Why are records logged on my End Date missing from the filtered results?

If your Access database column stores time values alongside dates, an exact match for the End Date evaluates to 12:00 AM (midnight) of that day. To capture the whole day, change your query logic to look for records less than the End Date plus one (< [EndDate] + 1).

Office Quick Solutions