Im trying to calculate a sum from 4 subreports in a headreport.
Im following the next steps:
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:
Now, how do you pass that total back to the the main report?
If the subreport is called Sub1, and the text box is txtTotal, put the text box on your main report, and start with this
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)
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)
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:Make sure the total text box is in the Report Footer section, not
the Page Footer section.
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.)
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
then repeat the whole expression in the total box, e.g.:
Stage 1 does not work
If the basic expression at Stage 1 above does not work: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.)
Uncheck the Name AutoCorrect boxes under:
Tools | Options | General
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.)
But at stage 2, im getting an error.
What im doing is first part1:
=[Active Directory Users met kosten Query subreport].[Report].[HasData]
After putting the iff experssion in and putting part 2 behind it, im having :
=IIf([Active Directory Users met kosten Query subreport].[Report].[HasData], [Active Directory Users met kosten Query
At this point im getting an error about a syntax?!
Can any1 help me with this pls?