How to Hide an Access Report Row When a Value Is Zero
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.

- 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.
Identify the exact control names (e.g., [Total Cases] or [SolidCases]) you wish to evaluate in your report before applying formatting scripts.
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.
Right-click your target report in the Navigation Pane and select 'Design View'.
Right-click the 'Detail' bar and choose 'Properties' to open the Property Sheet.
Navigate to the 'Event' tab in the Property Sheet. Click the ellipsis (...) next to the 'On Format' field and select 'Code Builder'.
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].
Save the VBA module, close the editor, and switch to 'Report View' or 'Print Preview' to verify the rows are hidden.

Filter the Report's Record Source
Use this method if you want to completely exclude records with zero values from the entire report, including from all calculations and grand totals.
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.

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.




