Others

Fix a Microsoft Access Query Missing Records in a Date Range

Khadija KhanKhadija Khan Nov 20, 2026 869 views

Question details

The user needs an Access query to return every record within a selected date range.

Product
Microsoft Access
Device & OS
not provided
Scenario
A query uses beginning and ending date parameters to filter a date field that may also contain times.
Observed behavior
The query omits some records in the selected range, potentially including records with times on the ending date.
Before you start

Identify the table and date field used by the query, and check whether the field contains time values. Enter the beginning and ending parameters as dates without times when running the revised query.

Solution 1Recommended

Use a Half-Open Date Range with DateTime Parameters in Access

Use this approach to include all times on the ending date while keeping the date field available for filtering.

A Between condition with an ending date at midnight can exclude records later that day. A half-open range includes values from the beginning date up to, but not including, midnight after the ending date.

1
Open the query in SQL View

In Microsoft Access, right-click the query in the Navigation Pane, select Design View, and then choose View > SQL View.

2
Declare the date parameters

At the beginning of the SQL statement, add: PARAMETERS [Beginning date] DATETIME, [Ending date] DATETIME;

3
Replace the date condition

In the WHERE clause, replace the Between condition with: [YourDateColumn] >= [Beginning date] AND [YourDateColumn] < [Ending date] + 1. Replace [YourDateColumn] with the actual date field name and retain any other required conditions.

4
Run and check the query

Select Run, enter the beginning and ending dates without times, and check that records from the full ending day appear.

Use a Half-Open Date Range with DateTime Parameters in Access
Other filters can still exclude records: If records are still missing, check the query's other criteria and joins, as well as the dates stored in the source field.
Free Microsoft Office alternative

Review Exported Access Data in WPS Spreadsheets

WPS Office is a free, lightweight alternative with a familiar interface and Microsoft Office format support for an easy transition. Export Access results to Excel to review them in WPS Spreadsheets; WPS does not fix or run Access queries.

Review exported query results and date columns in a spreadsheetOpen and edit Microsoft Excel XLSX filesUse familiar spreadsheet tools in a lightweight office suiteMove between supported Microsoft Office documents with ease
microsoft office alternative - wps office

Frequently Asked Questions

Why does Between miss records on the ending date?

An ending date entered without a time typically represents midnight at the start of that day. Records with later times on the same date fall beyond that value.

Does the revised condition include the entire ending day?

Yes. With a date-only ending parameter, [YourDateColumn] < [Ending date] + 1 includes times on the ending date but excludes midnight at the start of the following day.

Where do I declare the date parameters in Access?

Open the query in SQL View and place PARAMETERS [Beginning date] DATETIME, [Ending date] DATETIME; before the SELECT statement.

What should I check if the query still misses records?

Check the stored field values, other WHERE criteria, and table joins in Design View or SQL View. These can exclude records even when the date range is correct.