How to show Access subreport totals in main report

Display Access subreport totals in main report

Reports and aggregate functions go hand in hand. Using the same aggregate function, you can evaluate groups of data or the entire report. For example, the expression SUM(Amount) will return a different value depending on which report section it's in. Subreports expand on this capability. You can even display aggregate results from a subreport in your main report. The key is correct referencing.

To get started, add a control to the main report and set its Control Source property to a reference using the following form:


where control is the name of the aggregate-evaluating control in the subreport.

If the main report control displays #Error, you must deal with one of two problems: The subreport has no records to evaluate; the subreport control could be Null.

This next expression handles the recordless subreport problem:

=Iif(subform.Report.HasData, subform.Report.control, 0)

The Report HasData property is True if the report has data and False when there's none.

If the subreport aggregate is Null (which is highly unlikely, but go ahead and allow for the possibility), use this last expression:

=Iif(subform.Report.HasData, NZ(subform.Report.control, 0), 0)

During the development stage, I recommend that you stick with the simpler expression (the first one) because the two error-solving expressions can actually inhibit logic bugs.

About Susan Harkins

Susan Sales Harkins is an IT consultant, specializing in desktop solutions. Previously, she was editor in chief for The Cobb Group, the world's largest publisher of technical journals.

Editor's Picks

Free Newsletters, In your Inbox