Others

How to Hide Records in Access Reports While Including Totals

Maira Mehtab Sep 20, 2026 869 views

Question details

The user needs a display-only solution to hide specific detail records in an Access report based on their ID, while ensuring those hidden records are still included in the report's calculated totals.

Product: Microsoft Access
Device & OS: not provided
Scenario: Designing an Access report where certain rows (e.g., Service_ID 6, 15, and 16) must be hidden from the visual printout but their financial amounts still remain in the aggregate sums.
Observed behavior: Using a standard WHERE clause completely removes the data from the recordset, causing the amounts to be excluded from the report's grand totals.

Before you start

Ensure your Access report includes a Detail section and that you have enabled Design mode to access the VBA code editor for report events.

Solution 1Recommended

Use the Detail Section Format Event with VBA

This approach hides specific rows at display time without altering the underlying recordset, ensuring calculations like sums and counts remain accurate.

By intercepting the report's formatting process, you can prevent specific rows from rendering on the page. Because the data remains in the report's underlying record source, all mathematical functions in the report footers will continue to calculate normally.

Open Report in Design View

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

Access Detail Section Properties

Click on the Detail section bar to select it, then press F4 to open the Property Sheet.

Open the VBA Editor

Go to the Event tab in the Property Sheet, find the 'On Format' property, click the ellipsis (...) button, and choose Code Builder.

Enter the Cancellation Code

In the VBA window, enter the following code inside the formatting subroutine: Cancel = (ServiceID = 6 OR ServiceID = 15 OR ServiceID = 16). This prevents the row from rendering if it matches those IDs.

Test in Print Preview

Save your VBA code, close the editor, and switch your report to Print Preview to verify that the rows are hidden but the grand totals remain unchanged.

Calculation Verified: Any textbox in the report footer using a calculation like =Sum([Amount]) will naturally include the hidden records.

Solution 2

Use a Query WHERE Clause (To Exclude from Totals)

Use this method only if you actually want to remove the records from both the display and the report's aggregation totals.

Free Microsoft Office alternative

Looking for an Easy Way to Manage Data and Reports?

While Microsoft Access handles complex relational databases, WPS Office provides powerful Spreadsheet tools for most data tracking, filtering, and reporting needs. It is a free, lightweight alternative that seamlessly handles Microsoft Office file formats.

1. Download WPS Office: Visit the official WPS Office website and download the suite for your operating system.
2. Install and Open: Follow the installation prompts, open WPS Spreadsheet, and easily import your existing database exports.
3. Create Reports: Highlight your data and use the PivotTable feature to dynamically group, filter, and sum your records.

Fully compatible with Microsoft Excel (.xlsx) formats for seamless data migration.Create complex data reports using PivotTables without writing complex VBA code.Easily apply advanced filtering to hide rows while maintaining subtotal calculations.Free and lightweight with a familiar, user-friendly interface.

microsoft office alternative - wps office

Frequently Asked Questions

Why does adding a WHERE clause change my report totals?

A WHERE clause filters the data at the query level before it ever reaches the report. Because the records are excluded from the initial dataset, any sum or count operations in the report will ignore those records entirely.

Can I use Conditional Formatting to hide records instead of VBA?

Yes, you can use Conditional Formatting to set the font color to match the background color based on the Service ID. However, this leaves a blank white space on the report where the row would be, whereas the VBA 'On Format' method completely collapses the space.

Will the VBA Cancel method work in standard Access Report View?

No, the 'On Format' event only triggers when the report is printed or viewed in Print Preview. If you view the report in standard Report View, the supposedly hidden rows may still appear.

How do I calculate a sum of only the visible records?

To sum only visible records without using a query-level filter, you can use an IIf function in your text box control source, such as =Sum(IIf([ServiceID] Not In (6,15,16), [Amount], 0)).

Office Quick Solutions