Others

Fix Access ADODB Recordset Returning Wrong RecordCount for Aggregate Queries

Maira Mehtab Sep 22, 2026 869 views

Question details

The user needs to resolve an issue where an MS Access ADODB recordset executing an aggregate query returns a RecordCount of 1 instead of 0 when no matching records exist.

Product: Microsoft Access / VBA
Device & OS: not provided
Scenario: Executing an aggregate query, such as Min(), using ADODB recordsets to retrieve specific data points.
Observed behavior: The RecordCount property incorrectly returns 1 even when no source records match the query criteria, leading to processing errors.

Before you start

Ensure you have access to your database environment and that your VBA editor is open to modify the ADODB recordset logic.

Solution 1Recommended

Use IsNull Evaluation Instead of RecordCount

Avoid relying on the RecordCount property for aggregate queries by directly testing the returned field value for Null.

Aggregate queries (such as Min, Max, Sum, or Count) behave differently than standard SELECT queries. The database engine always generates at least one result row for an aggregate function. If no underlying data matches the query criteria, this returned row will contain a Null value.

Because a row is technically returned, the ADODB recordset registers a RecordCount of 1. Checking for RecordCount > 0 will fail to identify that the underlying data is empty. The correct approach is to test the value of the returned aggregate field.

Locate RecordCount logic

Open your VBA script or application code and find the line where you evaluate `objRecordset.RecordCount` or use `.EOF`.

Replace with IsNull test

Change the condition to evaluate the specific field. Use the syntax: `If IsNull(objRecordset.Fields(0).Value) Then`.

Handle the empty state

Inside the true condition of your If statement, add logic to handle the scenario where no records matched (for example, assign the current date or a default value).

Handle the populated state

In the Else block, add your standard logic to format and utilize the correctly returned date or value.

Best Practice: Consistently using IsNull() on fields generated by aggregate functions ensures robust data validation and prevents unexpected runtime errors in VBA.

Free Microsoft Office alternative

Looking for a Reliable Office Suite? Try WPS Office

While Microsoft Access requires specific VBA handling for database queries, managing your daily data, reports, and documentation doesn't have to be complicated. WPS Office provides a free, lightweight, and fully compatible alternative to Microsoft Office for your spreadsheets, documents, and presentations.

1. Download the installer: Visit the official WPS Office website to download the free installation package for your operating system.
2. Install the software: Run the downloaded installer and follow the simple on-screen instructions to set up the office suite.
3. Manage your data: Open WPS Spreadsheet to start managing, analyzing, and reporting on your data with high compatibility to Microsoft Excel.

Fully compatible with Microsoft Excel (.xlsx), Word (.docx), and PowerPoint (.pptx) formats.Free and lightweight alternative to heavy desktop office suites.Built-in advanced spreadsheet functions for powerful data analysis and reporting.Familiar user interface ensuring a seamless transition without learning curves.

microsoft office alternative - wps office

Frequently Asked Questions

Why does an aggregate query return a RecordCount of 1 when there is no data?

Aggregate functions like MIN() or MAX() are designed by the database engine to always return a result row, even if the source tables are empty or no rows match the WHERE clause. This single row contains a Null value, which ADODB counts as one record.

Can I use EOF and BOF to check for empty aggregate recordsets?

No, because the recordset actually contains one row (the Null row). Both .EOF (End of File) and .BOF (Beginning of File) will evaluate to False, making them ineffective for determining if underlying data matched an aggregate query.

How do I handle multiple aggregate fields in the same ADODB recordset?

If your query selects multiple aggregate fields, you generally only need to test one primary aggregate field using `IsNull(objRecordset.Fields("YourFieldName").Value)` to determine if matching source data existed.

Office Quick Solutions