Fix Access Query Returns Fewer Records Than Expected
Question details
Users experience an issue where a Microsoft Access database query fails to display the complete set of expected records.

- 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 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.
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.
Right-click your query in the Navigation Pane and select 'Design View'.
Find the column containing your date criteria in the query design grid.
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#`.
Click the 'Run' button (the red exclamation mark) in the ribbon to verify that the missing records now appear.

Modify Table Joins from Inner to Outer
Change how Access links your tables so that records aren't dropped when matching related data is missing.
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. Download the Installer: Visit the official WPS Office website and download the free installer for your operating system.
- 2. Install the Software: Run the downloaded file and follow the quick on-screen instructions to complete the setup.
- 3. Open Your Files: Launch WPS Office and directly open your existing Word, Excel, or PowerPoint files without losing formatting.

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.




