Fix a Microsoft Access Query Missing Records in a Date Range
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.
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.
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.
In Microsoft Access, right-click the query in the Navigation Pane, select Design View, and then choose View > SQL View.
At the beginning of the SQL statement, add: PARAMETERS [Beginning date] DATETIME, [Ending date] DATETIME;
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.
Select Run, enter the beginning and ending dates without times, and check that records from the full ending day appear.

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.

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.




