Formula in report and subreport

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

Guest

Hi

I posted previously about taking subtotals from a subreport and putting a grand total on the main report. I created a text box in the details section of the main report and put the following in the control source:
=IIf([RptSubAdLog].Report.HasData,[RptSubAdLog].Report!Company_Pledge,0). I then referenced this text box in the report footer. I was thrilled to see I did get a grand total, except it was not correct. In the subreport there are multiple companies grouped under different lead companies. They all have a company pledge and in the detail footer of the subreport is a total of each groups company pledges and this works fine. I used =Sum[Company_Pledge]. The expression I used for the grand total on the main report was only pulling the first company pledge of each group. I thought it would work if I total the =Sum[Company_Pledge] text box but did not work in the IIf formula I was using. Don't know if this is possible because it is already a calculation or if my syntex was wrong. Can anyone help me with this

Thank you. I feel I am getting close

Dorothy
 
Place a second textbox on the report next to the current group sum. Set its
Visible property to No, its control source to the same equation as your
current group total textbox, and its Running Sum property to Over All. In
the main part of the report, refer to this control to get your sum. The
control should already have the sum in it, so in the main report you would
just set the control to

=[Company_Pledge_Sum]

--
Wayne Morgan
MS Access MVP


Dorothy said:
Hi,

I posted previously about taking subtotals from a subreport and putting a
Click to expand...
grand total on the main report. I created a text box in the details section
of the main report and put the following in the control source:
=IIf([RptSubAdLog].Report.HasData,[RptSubAdLog].Report!Company_Pledge,0).
Click to expand...
I then referenced this text box in the report footer. I was thrilled to see
I did get a grand total, except it was not correct. In the subreport there
are multiple companies grouped under different lead companies. They all have
a company pledge and in the detail footer of the subreport is a total of
each groups company pledges and this works fine. I used
=Sum[Company_Pledge]. The expression I used for the grand total on the main
report was only pulling the first company pledge of each group. I thought it
would work if I total the =Sum[Company_Pledge] text box but did not work in
the IIf formula I was using. Don't know if this is possible because it is
already a calculation or if my syntex was wrong. Can anyone help me with
this?
 
A variation on my previous post.

In the main report, set the textbox to the summed textbox in the subreports
footer. Set the Running Sum for the textbox on the main report to Over All.
This should sum the values of each of the subreport's footer's sums.

--
Wayne Morgan
MS Access MVP


Dorothy said:
Hi,

I posted previously about taking subtotals from a subreport and putting a
Click to expand...
grand total on the main report. I created a text box in the details section
of the main report and put the following in the control source:
=IIf([RptSubAdLog].Report.HasData,[RptSubAdLog].Report!Company_Pledge,0).
Click to expand...
I then referenced this text box in the report footer. I was thrilled to see
I did get a grand total, except it was not correct. In the subreport there
are multiple companies grouped under different lead companies. They all have
a company pledge and in the detail footer of the subreport is a total of
each groups company pledges and this works fine. I used
=Sum[Company_Pledge]. The expression I used for the grand total on the main
report was only pulling the first company pledge of each group. I thought it
would work if I total the =Sum[Company_Pledge] text box but did not work in
the IIf formula I was using. Don't know if this is possible because it is
already a calculation or if my syntex was wrong. Can anyone help me with
this?
 
Hi

That is one of the problems. I do not know how to do this with a text box with a calculated formula. My original formula was =IIf([RptSubAdLog].Report.HasData,[RptSubAdLog].Report!Company_Pledge,0). The Company_Pledge would have to be replaced with =Sum[Company_Pledge], which is what totals the Company_Pledge and is located in the details footer of the subreport, and I cannot seem to get this to work without a syntax error.

Thank you for your help. I know this somehow has to work

Dorothy
 
Ok. let's try a different way. You can't Sum a calculated textbox. Report
textboxes have an option to create a Running Sum and so they'll do the work
for you.

The other option is to do the original calculation for Company_Pledge in the
query feeding the report by creating a calculated field in the query. Bind
the control in the report to this calculated field. You now have a field
that you can use Sum on.

--
Wayne Morgan
MS Access MVP


Dorothy said:
Hi,

That is one of the problems. I do not know how to do this with a text box
Click to expand...
with a calculated formula. My original formula was
=IIf([RptSubAdLog].Report.HasData,[RptSubAdLog].Report!Company_Pledge,0).
The Company_Pledge would have to be replaced with =Sum[Company_Pledge],
which is what totals the Company_Pledge and is located in the details footer
of the subreport, and I cannot seem to get this to work without a syntax
error.
 
Back
Top