Others

How to Hide an Access Report Row When a Value Is Zero

Nimra MalikNimra Malik Nov 3, 2026 870 views

Question details

The user wants to suppress or hide a detail row in a Microsoft Access report when specific field values (like Total Cases) are zero or null.

How to Hide an Access Report Row When a Value Is Zero
Product
Microsoft Access
Device & OS
not provided
Scenario
Generating or formatting reports where records with zero or null values need to be hidden to save space and improve readability.
Observed behavior
Report rows containing zero or null values are displayed by default, taking up unnecessary space on the printed or previewed report.
Before you start

Identify the exact control names (e.g., [Total Cases] or [SolidCases]) you wish to evaluate in your report before applying formatting scripts.

Solution 1Recommended

Hide the Detail Row Using the Format Event in VBA

Use this method to dynamically suppress rows during report generation without removing the data from underlying summary calculations.

By tapping into the Detail section's Format event via VBA, you can instruct Access to cancel the formatting (and rendering) of a specific row if the target field equals zero or is null.

1
Open Report in Design View

Right-click your target report in the Navigation Pane and select 'Design View'.

2
Access Detail Section Properties

Right-click the 'Detail' bar and choose 'Properties' to open the Property Sheet.

3
Open the VBA Code Builder

Navigate to the 'Event' tab in the Property Sheet. Click the ellipsis (...) next to the 'On Format' field and select 'Code Builder'.

4
Enter the Formatting Code

Inside the Private Sub Detail_Format module, enter the following code: Cancel = (Nz(Me.[YourControlName], 0) = 0). Ensure you replace 'YourControlName' with your actual field, like [Total cases].

5
Save and Preview

Save the VBA module, close the editor, and switch to 'Report View' or 'Print Preview' to verify the rows are hidden.

Hide the Detail Row Using the Format Event in VBA
Preventing Error 94: Invalid Use of Null: If you use Cancel = (Me.[SolidCases] = 0) and the field contains a Null value instead of 0, you will get Error 94. Wrapping the control in the Nz() function, such as Nz(Me.[SolidCases], 0), converts nulls to zero to safely evaluate the expression.
Free Microsoft Office alternative

Looking for a Lightweight Alternative for Your Office Tasks?

While WPS Office does not currently include a direct database management tool like Microsoft Access, it is an exceptional, free alternative for your daily word processing, spreadsheet, and presentation needs. It's fully compatible with Microsoft Office formats and incredibly lightweight.

Seamless compatibility with Microsoft Excel (.xlsx), Word (.docx), and PowerPoint (.pptx).Powerful spreadsheet features to handle data analysis and reporting as a lightweight alternative to basic Access queries.Free to download and use with a highly intuitive, familiar user interface.Built-in PDF editing and format conversion tools to easily export your reports.
microsoft office alternative - wps office

Frequently Asked Questions

Why do I get 'Error 94: Invalid use of Null' when hiding rows in Access?

This error occurs because VBA cannot directly evaluate a Null value in a mathematical equation. If the field is blank (Null) rather than containing a numerical zero, the equation breaks. To fix this, wrap your control name in the Nz() function, which converts Nulls to a specified value: `Cancel = (Nz(Me.[ControlName], 0) = 0)`.

Will hiding the detail row shrink the size of the report section?

Yes. When you set 'Cancel = True' in the On Format event of the Detail section, Access cancels the rendering of that specific section for the current record. This prevents blank spaces from appearing, effectively shrinking the report and saving space.

What is the difference between filtering the record source and using the Format event?

Filtering the record source completely removes the data from the report's underlying dataset, meaning those records won't be included in any report grand totals or calculations. Using the Format event only hides the row visually on the page, but the underlying data remains available for summary functions like Sum() or Count() in the report footers.