How do I sum a total from multiple sub reports onto a main report

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am trying to sum amounts contained in sub-reports on to the main report
that contains the sub-reports. Is this possible? If so, does anyone know
what the syntax is?

thanks
 
Skiman,

In an unbound tetxbox on the main form, enter its Control Source like
this...

=[SubReport1]![1stAmount]+[SubReport2]![2ndAmount]+[SubReport3]![3rdAmount]

This assumes the applicable controls on all subreports will always have
a value, otherwise you will need some refinements to cater to Nulls or
subreports with no data.
 
Dear Steve,

Saw your post about this and I am having the trouble you mentioned about
sub-reports having no values, therefore I am getting a #error message. Can
you tell me how I get around this?
--
CotoJoe


Steve Schapel said:
Skiman,

In an unbound tetxbox on the main form, enter its Control Source like
this...

=[SubReport1]![1stAmount]+[SubReport2]![2ndAmount]+[SubReport3]![3rdAmount]

This assumes the applicable controls on all subreports will always have
a value, otherwise you will need some refinements to cater to Nulls or
subreports with no data.

--
Steve Schapel, Microsoft Access MVP

I am trying to sum amounts contained in sub-reports on to the main report
that contains the sub-reports. Is this possible? If so, does anyone know
what the syntax is?

thanks
Click to expand...
Click to expand...
 
CotoJoe said:
Saw your post about this and I am having the trouble you mentioned about
sub-reports having no values, therefore I am getting a #error message. Can
you tell me how I get around this?
Click to expand...


Check the subreport's HasData property:

=IIf(subreport.Report.HasData, subreport.Report.textbox, 0)
 
Dear Marsh:
Thanks very much for your post.
--
I tried this but it didn't work. Forgive me I am new to Access. I have a
main report named rptInvoice with 2 subforms, rptFloaterTrans and
rptTaxesSumProp. In the HasData section of the sub-form (not the main form
property), I typed
IIf(subreport.rptFloaterTrans.HasData, subreport.rptFloaterTrans.textbox,
0). After I exited the expression it put [] around the words "subreport" and
[rptFloaterTrans.HasData] .

Am I typing this in the right place the right way? Should I put the
expression on the main report property HasData section?

Thanks again.
 
CotoJoe said:
I tried this but it didn't work. Forgive me I am new to Access. I have a
main report named rptInvoice with 2 subforms, rptFloaterTrans and
rptTaxesSumProp. In the HasData section of the sub-form (not the main form
property), I typed
IIf(subreport.rptFloaterTrans.HasData, subreport.rptFloaterTrans.textbox,
0). After I exited the expression it put [] around the words "subreport" and
[rptFloaterTrans.HasData] .

Am I typing this in the right place the right way? Should I put the
expression on the main report property HasData section?
Click to expand...


There is no such thing as a HasData section so I have no
idea what you are referring to. The place to put the
expression is in a main report text box's ControlSource
property.

You seem to have misunderstood what you need to replace with
your own names. The text box's expression would be

=IIf(rptFloaterTrans.REPORT.HasData,
rptFloaterTrans.REPORT.textboxinrptFloaterTrans, 0) +
IIf(rptTaxesSumProp.REPORT.HasData,
rptTaxesSumProp.REPORT.textboxinrptTaxesSumProp, 0)

Note that the subreport names used here are the names of the
subreport **controls** on the main report. These names may
be different from the name of the report object they are
displaying.
 
Marshall,

Thanks again and I mispoke. I was talking about the "On No Data" event on
the sub-report, my apologies for not being clear. I will give your coding a
try and see if I can get it right this time. Will post back my results and
again thank you very much for your help.
--
CotoJoe


Marshall Barton said:
CotoJoe said:
I tried this but it didn't work. Forgive me I am new to Access. I have a
main report named rptInvoice with 2 subforms, rptFloaterTrans and
rptTaxesSumProp. In the HasData section of the sub-form (not the main form
property), I typed
IIf(subreport.rptFloaterTrans.HasData, subreport.rptFloaterTrans.textbox,
0). After I exited the expression it put [] around the words "subreport" and
[rptFloaterTrans.HasData] .

Am I typing this in the right place the right way? Should I put the
expression on the main report property HasData section?
Click to expand...


There is no such thing as a HasData section so I have no
idea what you are referring to. The place to put the
expression is in a main report text box's ControlSource
property.

You seem to have misunderstood what you need to replace with
your own names. The text box's expression would be

=IIf(rptFloaterTrans.REPORT.HasData,
rptFloaterTrans.REPORT.textboxinrptFloaterTrans, 0) +
IIf(rptTaxesSumProp.REPORT.HasData,
rptTaxesSumProp.REPORT.textboxinrptTaxesSumProp, 0)

Note that the subreport names used here are the names of the
subreport **controls** on the main report. These names may
be different from the name of the report object they are
displaying.
Click to expand...
 
Back
Top