Others

Fix Access Query Returns Fewer Records Than Expected

Natalie TaylorNatalie Taylor Nov 2, 2026 869 views

Question details

Users experience an issue where a Microsoft Access database query fails to display the complete set of expected records.

How to Fix Access Query Returning Fewer Records Than Expected
Product
Microsoft Access 2016
Device & OS
not provided
Scenario
Running an Access query containing date criteria and table joins to retrieve specific database records for a report.
Observed behavior
The query unexpectedly returns fewer records than it should. The missing records only become visible when the user applies a filter, sorts the datasheet, or opens the data via an Access report.
Before you start

Before modifying your queries, ensure you understand the specific date ranges you are trying to pull and verify whether your database tables store time values alongside dates.

Solution 1Recommended

Use Half-Open Intervals for Date Criteria

Adjust your query criteria to account for hidden time values that cause records on the end date to be excluded.

In Access, date fields often contain hidden time portions (e.g., '11/15/2023 14:30:00'). When you use a standard BETWEEN criteria for date ranges, Access assumes the time is exactly midnight (00:00:00) on the end date. Any record with a time later than midnight on that final day is dropped from the results.

Using a half-open interval avoids this overlap and gap issue by capturing everything up to the very start of the day after your intended range.

1
Open Query in Design View

Right-click your query in the Navigation Pane and select 'Design View'.

2
Locate the Date Field

Find the column containing your date criteria in the query design grid.

3
Update the Criteria Logic

Instead of typing `Between [StartDate] And [EndDate]`, replace your criteria with `>= [StartDate] And < [NextStartDate]`. For example, if you want records through the end of November 15, set it to `< #11/16/2023#`.

4
Run the Query

Click the 'Run' button (the red exclamation mark) in the ribbon to verify that the missing records now appear.

Use Half-Open Intervals for Date Criteria
Criteria Verification: This method ensures that absolutely all records falling on the final date, regardless of their specific time stamp, are successfully retrieved.
Free Microsoft Office alternative

Looking for a Lightweight Office Suite?

While complex database queries require tools like Microsoft Access, managing everyday lists, data tracking, and reports is often faster and easier in spreadsheet software. If you are looking for a reliable, free alternative to Microsoft Office for your documents, spreadsheets, and presentations, WPS Office is an excellent choice.

  1. 1. Download the Installer: Visit the official WPS Office website and download the free installer for your operating system.
  2. 2. Install the Software: Run the downloaded file and follow the quick on-screen instructions to complete the setup.
  3. 3. Open Your Files: Launch WPS Office and directly open your existing Word, Excel, or PowerPoint files without losing formatting.
Highly compatible with Microsoft Excel (.xlsx), Word (.docx), and PowerPoint (.pptx) formats.Free to download and use with a clean, familiar interface.Lightweight design that runs smoothly on older hardware.Built-in PDF editing, merging, and conversion tools.Seamless data management for lists and reports without writing complex SQL.
microsoft office alternative - wps office

Frequently Asked Questions

Why do missing Access records suddenly appear when I apply a filter or sort?

This anomaly is often tied to how Access processes and indexes data in the background, especially over network connections or with linked tables. Applying a filter or a sort forces the Access database engine to fetch the complete recordset from the source rather than relying on a partially loaded or cached view.

How can I check if my date fields secretly contain time values?

Open the table containing the dates in Design View, select the date field, and change its 'Format' property in the bottom pane to 'General Date'. When you view the table in Datasheet View, any hidden time components attached to your dates will now be fully visible.

Can linked tables cause a query to return fewer records?

Yes. If you are querying linked tables—especially those connected to an external SQL server or a slow network backend—network latency or mismatched data types can cause Access to timeout or return an incomplete recordset until the data is fully requeried.