VBA & Macro Problems

Fix Access VBA RecordCount Returning 1 for Empty Aggregate Queries

Rana Garcia Sep 28, 2026 869 views

Question details

The ADODB.Recordset returns a record count of 1 when executing an aggregate query, even if no records match the criteria.

How to Fix Access VBA RecordCount Returning 1 for Empty Aggregate Queries

Product: Microsoft Access VBA
Device & OS: not provided
Scenario: Executing an aggregate SQL query (such as Min or Max) through an ADODB.Recordset in VBA and attempting to verify if records exist using the RecordCount property.
Observed behavior: The RecordCount property evaluates to 1 instead of 0 for empty results, causing downstream script logic to fail or misinterpret the data.

Before you start

Ensure your database connection is active and stable, and back up your current VBA module code before modifying your recordset evaluation logic.

Solution 1Recommended

Evaluate the Field for Null Instead of Using RecordCount

Since aggregate functions always return a single row containing a Null value for empty data sets, explicitly checking the field for Null is the most reliable workaround.

When utilizing an aggregate function like Min, Max, or Sum in an ADODB.Recordset, the SQL database engine always returns a single calculated result row. If there is no underlying data that matches your criteria, this single row evaluates to Null. Because a row is technically returned, the RecordCount property correctly reports 1, which can be misleading for conditional logic.

Locate the query execution block

Open your VBA editor and find the section of code where you open the ADODB.Recordset and execute the aggregate query.

Replace the RecordCount condition

Remove your existing logic that checks if RecordCount is greater than 0. Instead, implement an IsNull check on the first field using: If IsNull(objRecordset.Fields(0).Value) Then

Add fallback logic for Null values

Within the True block of your If statement, add logic to handle the empty state, such as assigning a fallback date or exiting the function entirely.

Process valid data

Create an Else block to format and process the returned data when the field is not Null, ensuring your script runs smoothly when valid records exist.

Evaluate the Field for Null Instead of Using RecordCount

Best Practice: Always explicitly validate the field value with IsNull when processing aggregate queries, as EOF and BOF properties will also inaccurately reflect an empty state.

Free Microsoft Office alternative

Enhance Your Data Workflows with WPS Office

While WPS Office does not include a direct Microsoft Access equivalent, WPS Spreadsheet offers advanced data manipulation, robust VBA macro support, and powerful pivot tables to handle complex data tasks effortlessly and for free.

1. Download the software: Visit the official WPS Office website and click the free download button.
2. Install WPS Office: Run the downloaded installer and follow the quick setup instructions.
3. Automate with macros: Launch WPS Spreadsheet and leverage the built-in VBA editor to manage your complex data processing efficiently.

Fully compatible with Microsoft Excel formats (.xlsx, .xlsm, .csv)Supports advanced VBA macros for extensive workflow automationLightweight software package ensuring high performance on any deviceCompletely free Office suite featuring a clean, familiar interface

microsoft office alternative - wps office

Frequently Asked Questions

Why does an aggregate SQL query return a row when there is no matching data?

In SQL, aggregate functions like MIN(), MAX(), or SUM() are designed to perform a calculation and return a single result. If the data set is completely empty, the function's result evaluates to Null, which is delivered as a single row in your recordset.

Does this RecordCount issue happen with regular SELECT queries?

No. Standard SELECT queries that do not use aggregate functions will correctly return an empty recordset with a RecordCount of 0 when no matching records are found in the database.

Can I use the EOF and BOF properties instead of RecordCount to check for empty results?

For aggregate queries, EOF (End of File) and BOF (Beginning of File) will both return False. This happens because the recordset actually contains one row (the row holding the Null value), so you cannot rely on EOF/BOF either.

Are there alternative methods to checking for Null in VBA?

You could write a separate COUNT() query to check the number of matching records before executing your main aggregate function. However, using IsNull on the aggregate result is much more efficient as it requires only one call to the database.

Excel & Data Problems