Change Query Top Values Property with VBA ~ LEARN MS-ACCESS TIPS AND TRICKS

Introduction.

We have already learned how to use the Top Values property of Queries (applicable in SELECT, MAKE-TABLE, and APPEND queries) in an earlier post. If you haven’t seen it yet, I recommend taking a look at that article first [link here].

The Top Values property of a Query can only be set manually during design time. Unfortunately, you cannot directly change its value dynamically in VBA. You also cannot expect users to open the query in Design View each time they want a different set of results.

So, how do we handle situations where we need flexibility with these values?

Sample SQL with TOP Property Setting.

When we change the Top Values property manually, Microsoft Access automatically updates the SQL statement of the Query to reflect that change. Keeping this behavior in mind, we can use a little VBA trickery to manipulate the SQL directly, rather than searching for a property setting that doesn’t exist.

Before we move on to that approach, let us first examine what actually happens to the SQL definition of a Query when you set the Top Values property or other related properties.

Here is a sample SQL of a SELECT Query with the Top Values-Property set to the Value 25.

SELECT TOP 25 Orders.OrderID, Orders.EmployeeID, Orders.OrderDate, Orders.RequiredDate, Orders.Freight
FROM Orders
ORDER BY Orders.Freight DESC;

As shown in the example, when the Top Values property is set to 25, Access automatically inserts the text TOP 25 immediately after the SELECT clause in the SQL string. This indicates that the Query will return only 25 records. In the ORDER BY clause, the Freight column is sorted in descending order, so the output consists of the 25 records with the highest freight values in the table.

If, instead of a fixed number of records, you want a percentage of the total records—for example, 25%—then the Top Values property must be set to 25% rather than 25. In this case, the SQL text changes accordingly to:


SELECT TOP 25 PERCENT Orders.OrderID, Orders.EmployeeID, Orders.OrderDate, Orders.RequiredDate, Orders.Freight

FROM Orders
ORDER BY Orders.Freight DESC;

The next property that affects the record set is the Unique Values property (valid values: Yes or No). When this property is set to Yes, Access suppresses duplicate records in the output shown in the datasheet. However, it only evaluates the fields included in the Query’s column list—other fields from the table that are not selected are ignored when checking for duplicates.

When this property is enabled, Access inserts the keyword DISTINCT in the SQL, immediately after the SELECT clause. The modified SQL will look like this:

SELECT DISTINCT TOP 25 PERCENT Orders.OrderID, Orders.EmployeeID, Orders.OrderDate, Orders.RequiredDate, Orders.Freight
FROM Orders
ORDER BY Orders.Freight DESC;

Another property you can set in a query is Unique Records (valid values: Yes or No). When this property is set to Yes, Access suppresses duplicate records in the output. Unlike the Unique Values property, this setting evaluates all fields in the source table, regardless of whether they are included in the query’s column list.

When enabled, the SQL changes by replacing the DISTINCT keyword with DISTINCTROW. This ensures that the uniqueness check is applied across the entire underlying table.

For example:

SELECT DISTINCTROW TOP 25 PERCENT Orders.OrderID, Orders.EmployeeID, Orders.OrderDate, Orders.RequiredDate, Orders.Freight
FROM Orders
ORDER BY Orders.Freight DESC;

We will exclude the Unique Records property from our VBA-based solution. As shown in the examples above, depending on the user’s requirements, we can dynamically add or remove any of these three elements—DISTINCT, TOP n, or PERCENT—to control the query output for reports.

Preparing for the VBA-based Solution.

Our methodology for modifying the SQL is straightforward. We will collect the required Query property values from the user through a TextBox and a Checkbox placed on a Form. Once the user provides the input and clicks a Command Button, the SQL of the Query will be redefined accordingly. This will invoke the following actions to redefine the Query:

Open the Query Definition and read the existing SQL String.
Scan the SQL string and look for the text DISTINCT, TOP nn, and PERCENT. If found, then remove them from the SQL String.
Validate the input given by the User in the Textbox and checkbox, and insert appropriate SQL Clauses in the SQL String.
Update the modified SQL in the Query definition.

This article has become too long now. Explaining the above four steps and introducing the VBA Routines may make it even longer. We will complete this topic in the next blog post.