Others

How to Fix Access Report Error 3464 When a Group Has No Records

Nimra Malik Oct 22, 2026 868 views

Question details

The user encounters Error 3464 (Data Type Mismatch in Criteria Expression) in an Access report when grouping records, particularly when a client group contains no open invoices.

How to Fix Access Report Error 3464 When a Group Has No Records

Product: Microsoft Access
Device & OS: not provided
Scenario: Generating a grouped report of open invoices by client where some clients have no data.
Observed behavior: The report returns Error 3464 due to improper handling of Null values or attempting to aggregate calculated controls directly.

Before you start

Verify the names of the fields and controls in your report, and open the underlying query in Design View to prepare for modifications.

Solution 1Recommended

Apply the Nz Function in the Query's SELECT Statement

Moving the Nz function to the underlying query prevents the report from processing Null values, avoiding data type mismatch errors.

Handling Null values directly inside the report's text boxes often leads to mismatch errors when aggregations happen. By utilizing the Nz function in the query, the report receives actual zeros instead of Nulls.

Open the query in Design View

Navigate to the Navigation Pane in your Access database, right-click the query that acts as the record source for your report, and select 'Design View'.

Apply the Nz function to the field

Locate the field causing the error (e.g., DaysOS). Wrap the field expression in the Nz function to convert Nulls to zeros: Nz([DaysOS], 0) AS DaysOS.

Save and test the report

Save the changes to your query, switch to 'Datasheet View' to verify the zeros are appearing correctly, and then open your report to confirm the error is resolved.

Apply the Nz Function in the Query's SELECT Statement

Tip: Applying data transformations at the query level is generally more robust and improves report processing stability.

Solution 2

Fix Aggregated Calculated Report Controls

Access cannot aggregate a report control if its Control Source is another expression.

Solution 3

Filter Out Records with No Open Invoices

If you do not need to display clients with no open invoices, filtering them out bypasses the error completely.

Free Microsoft Office alternative

Looking for a Free, Lightweight Office Suite?

While WPS Office does not include a database management tool like Microsoft Access, it provides a powerful, free alternative to Word, Excel, and PowerPoint. Experience seamless compatibility with Microsoft Office formats in a lightweight and easy-to-use interface.

1. Download the installer: Visit the official WPS Office website and click the free download button.
2. Install the software: Run the downloaded installer and follow the on-screen instructions to complete the setup.
3. Open your files: Launch WPS Office and seamlessly open, edit, and save your existing Office documents.

Fully compatible with Microsoft Excel (.xlsx), Word (.docx), and PowerPoint (.pptx) formatsLightweight architecture that consumes minimal system resourcesFamiliar user interface for a seamless migrationIncludes a built-in PDF reader and editor

microsoft office alternative - wps office

Frequently Asked Questions

What causes Error 3464 Data Type Mismatch in Access?

This error typically occurs when a query or report criteria compares fields of incompatible data types, such as comparing a text string to a number, or improperly handling Null values in an aggregate function.

What is the Nz function used for in Microsoft Access?

The Nz function is used to convert a Null value to a zero, an empty string, or another specified value. This prevents mathematical operations and reports from failing when data is missing.

Can I use an aggregate function on a calculated control in an Access report?

No, Microsoft Access does not allow you to aggregate a control whose Control Source is an expression. You must repeat the underlying expression inside the aggregate function itself.

How does the Avg function handle Null values in Access?

By default, the Avg function in Access ignores Null values when calculating the average of a dataset. If you convert those Null values to zeros using the Nz function, they will be included in the calculation and will lower the average.

Office Quick Solutions