Others

Fix MS Access Query Returning Dates Outside Specified Range

Guest Writer Oct 12, 2026 869 views

Question details

The user needs to correct a database query that returns records with dates falling outside the requested date range.

Fix MS Access Query Returning Dates Outside Specified Range

Product: Microsoft Access
Device & OS: not provided
Scenario: Filtering database records in a query using a specific date range defined by form inputs.
Observed behavior: The query incorrectly returns dates outside the specified boundaries because the Date/Time field is being formatted as text, causing the database to perform alphabetical text comparisons instead of chronological date comparisons.

Before you start

Open your database in Design View and locate the specific query that is returning incorrect date records. Identify the exact raw Date/Time field names and the form parameters you are using to define the date range.

Solution 1Recommended

Filter the Original Date Field with Inclusive and Exclusive Boundaries

Avoid formatting the date field in the WHERE clause. Filter the raw Date/Time field directly using greater-than-or-equal-to and less-than operators to ensure correct chronological comparison.

When you use the Format() function on a Date/Time field, the database engine converts the chronological date into a text string. This forces the query to evaluate parameters alphabetically, which leads to records outside your target range being included.

To resolve this, apply the date range criteria directly to the raw Date_Started field. Use the start date inclusively (>=) and the day after your target end date exclusively (<).

Open Query in Design or SQL View

Right-click your target query in the navigation pane and select 'Design View' or 'SQL View'.

Remove Format() from the WHERE Clause

Locate the WHERE clause. Remove any Format() functions applied to your date field. Only use Format() in the SELECT list if you need to control how the date is displayed to the user.

Apply Inclusive and Exclusive Boundaries

Update your WHERE clause to compare the raw date field against your form inputs. Example: WHERE Date_Started >= [Forms]![frm_Previous_Day_Count]![txt_FromDate] AND Date_Started < [Forms]![frm_Previous_Day_Count]![txt_ToDate] + 1;

Run and Verify

Click the 'Run' button on the Design ribbon to execute the query and verify that only records strictly within the specified date range are returned.

Filter the Original Date Field with Inclusive and Exclusive Boundaries

Preserving Index Usage: Filtering the raw Date/Time field rather than a formatted text alias allows the database to utilize table indexes, which significantly speeds up query execution on large datasets.

Solution 2

Use the Int() Function to Strip Time Values

Use the Int() function to safely remove the time portion of a Date/Time field for date-only comparisons without converting the data into text.

Free Microsoft Office alternative

Looking for a Lightweight and Free Office Suite?

While WPS Office does not include a direct alternative to database management systems like MS Access, it offers a complete, free, and lightweight solution for all your everyday document, spreadsheet, and presentation needs. Enjoy a familiar interface and seamless compatibility with Microsoft Office formats without the hefty subscription fees.

1. Download: Visit the official WPS Office website and click the free download button.
2. Install: Run the downloaded installer and follow the simple on-screen instructions to set up WPS Office on your computer.
3. Use: Open WPS Office to start creating or editing your Word, Excel, and PowerPoint files instantly.

Fully compatible with Microsoft Excel (.xlsx), Word (.docx), and PowerPoint (.pptx) formats.Lightweight software that uses minimal system resources and runs smoothly on older devices.Built-in advanced PDF editing tools to view, annotate, merge, and convert PDF files effortlessly.Familiar tabbed interface that makes transitioning from Microsoft Office seamless and quick.

microsoft office alternative - wps office

Frequently Asked Questions

Why does formatting a date field ruin my date range query?

Using the Format() function inside a WHERE clause converts the numerical Date/Time value into a text string. The database engine then compares your form parameters alphabetically rather than chronologically, causing unexpected records to be included in your results.

How do I filter a date range while ignoring the time portion?

To ignore the time portion safely, use the Int() function on your Date/Time field (e.g., Int([Date_Field])). This strips the time component while preserving the underlying chronological data type, ensuring operators like >= and < function correctly.

Why is my query running so slowly when filtering by dates?

If you apply functions like Format() directly to a table field in the WHERE clause, the database engine cannot use the indexes built for that field. Comparing the raw Date/Time field against correctly structured parameter boundaries allows index usage, vastly improving query speed.

Office Quick Solutions