Microsoft Access Tips for Casual Users

Provided by allenbrowne.com, June 2006.

Bring the total from a subreport back onto the main report

Your subreport has a total at the end - a text box in the Report Footer section, with a Control Source like this:
    =Sum([Amount])
Now, how do you pass that total back to the the main report?

Stage 1

If the subreport is called Sub1, and the text box is txtTotal, put the text box on your main report, and start with this Control Source:
    =[Sub1].[Report].[txtTotal]

Stage 2

Check that it works. It should do if there are records in the subreport. If not, you get #Error. To avoid that, test the HasData property, like this:
    =IIf([Sub1].[Report].[HasData], [Sub1].[Report].[txtTotal], 0)

Stage 3

The subreport total could be Null, so you might like to use Nz() to convert that case to zero also:
    =IIf([Sub1].[Report].[HasData], Nz([Sub1].[Report].[txtTotal], 0), 0)

Troubleshooting

If you are stuck at some point, these further suggestions might help.

Total does not work in the subreport

If the basic =Sum([Amount]) does not work in the subreport:

1. Make sure the total text box is in the Report Footer section, not the Page Footer section.
2. Make sure the Name of this text box is not the same as the name of a field (e.g. it cannot be called Amount.)
3. The field you are trying to sum must be a field in the report's source table/query. If Amount is a calculated text box such as:
       =[Quantity]*[PriceEach]
   then repeat the whole expression in the total box, e.g.:
       =Sum([Quantity]*[PriceEach])
4. Make sure that what you are trying to sum is a Number, not text. See Calculated fields misinterpreted.

Stage 1 does not work

If the basic expression at Stage 1 above does not work:

1. Open the main report in design view.
   Right-click the edge of the subform control, and choose Properties.
   Check the Name of the subreport control (on the Other tab of the Properties box.)
   The Name of the subreport control can be different than the name of the report it contains (its Source Object.)
2. Uncheck the Name AutoCorrect boxes under:
       Tools | Options | General
   For details of why, see Failures caused by Name Auto-Correct

Stage 2 does not work

If Stage 2 does not work but Stage 1 does, you must provide 3 parts for IIf():

• an expression that can be True or False (the HasData property in our case),
• an expression to use when the first part is True (the value from the subreport, just like Stage 1),
• an expression to use when the first part is False (a zero.)