The Setting That Silently Limits Microsoft Access Query Results That You Might Not Know Exists QQ#78

Ever run a query in Microsoft Access, know for a fact there are hundreds of records behind it, and yet Access politely hands you… 99? No error. No warning. Just a quiet little ceiling on your data. If that has ever happened to you, you are not losing your mind, and your database is not broken. You have just run into one of those tiny hidden settings that can cause a whole lot of confusion if you do not know where to look.

The culprit is something called the Top Values setting. It lives right up in your query design tools, and it controls how many records Access will return. Most of the time it is set to "All," which is exactly what you want. But if it ever gets changed, intentionally or accidentally, your query will quietly stop returning results once it hits that limit. No alarms. No flashing lights. It just stops.

Let’s look at how this happens. Open any query in Design View and look at the ribbon. You will see a dropdown labeled Top Values. If that box says 10, 50, 99, or any other number, that is your cap. Access will return only that many rows, even if your underlying table has thousands.

This setting often gets changed during testing. For example, you might temporarily set Top Values to 100 while building or troubleshooting a query so it runs faster. Then later you forget to set it back. The query works fine, but now your reports or forms that rely on it are mysteriously missing data.

If you switch over to SQL View, you will see this expressed as a clause in the statement itself. It looks like this:

SELECT TOP 99 * FROM Customers;

That TOP keyword is the limiter. Remove it, or change the Top Values dropdown back to All, and suddenly your missing records reappear like magic.

This becomes especially important when queries feed other objects. If you build a form or report on top of a limited query, the limitation carries through. The form is not broken. The report is not filtering incorrectly. It is simply inheriting the row cap from the query underneath.

Another common beginner mistake is assuming the query is filtering data because of criteria, joins, or grouping. People start tearing apart relationships and WHERE clauses trying to fix something that is not even the problem. Always check Top Values first when record counts look suspiciously round.

While we are on the subject of quiet database gotchas, this came up alongside a few related discussions recently. For example, using SharePoint as a backend can introduce its own limitations and performance quirks compared to SQL Server. Another frequent issue involves parameter prompts. If multiple queries all ask for the same date range, the better approach is to pull those values from form controls instead of repeated popups. Different problem, same theme. Small design choices can have big usability consequences.

The big takeaway here is simple. When query results look incomplete, do not panic. Do not assume corruption. Check the basics first. Top Values is one of those tiny switches that can have an outsized impact on what you see.

If you want to watch the full walkthrough, including where to find the setting and how it behaves in real time, the video above shows it step by step. But now you know what to look for, and more importantly, why it happens.

Live long and prosper,
RR