Subreports

[Melinda]

I am trying to bring a subreport total, which is a calculated total, to the
main report. When it carries over to the main report it is not a sum of
figures. It only reflects the entry. I have a sum from the main report,
txtbudgetsum, which is correct, when I try to subtract the expenditures from
this it only picks up the first expenditure. Hopefully someone can guide me
in the right direction. I have read post after post with wrong results.
Thanks in advance

 

[Marshall Barton]

I am trying to bring a subreport total, which is a calculated total, to the
main report. When it carries over to the main report it is not a sum of
figures. It only reflects the entry. I have a sum from the main report,
txtbudgetsum, which is correct, when I try to subtract the expenditures from
this it only picks up the first expenditure.

You need to calculate the sum in the subreport's Report
Header or Footer section using a text box (named
txtExpendSubtotal) with an expression like:
=Sum(expenditure)

Then the main report text box can display what I think you
want by using an expression like:
=budgetsum - IIf([subreport control].Report.HasData,
[subreport control].Report.txtExpendSubtotal, 0)

 

[Melinda]

When I add the text box to the report footer, (totalpo), I get a parameter
value that pops up and of course then I get no results. I have a SAC
Header and Footer and that is where I have the running sum for the subreport
and it indeeds sums up correctly. I have =[totalpo] with a running sum for
the report. For some reason it doesn't seem to work in the report footer.
I am sure I am doing something wrong. I did a text box with =sum[totalpo]
and I get nothing.

I am trying to bring a subreport total, which is a calculated total, to the
main report. When it carries over to the main report it is not a sum of
figures. It only reflects the entry. I have a sum from the main report,
txtbudgetsum, which is correct, when I try to subtract the expenditures from
this it only picks up the first expenditure.

You need to calculate the sum in the subreport's Report
Header or Footer section using a text box (named
txtExpendSubtotal) with an expression like:
=Sum(expenditure)

Then the main report text box can display what I think you
want by using an expression like:
=budgetsum - IIf([subreport control].Report.HasData,
[subreport control].Report.txtExpendSubtotal, 0)

 

[Melinda]

Went back and tried again I got it to work, was using the wrong expression.
I will post back, I am going to the main report to try that. Thanks for all
your help.

I am trying to bring a subreport total, which is a calculated total, to the
main report. When it carries over to the main report it is not a sum of
figures. It only reflects the entry. I have a sum from the main report,
txtbudgetsum, which is correct, when I try to subtract the expenditures from
this it only picks up the first expenditure.

You need to calculate the sum in the subreport's Report
Header or Footer section using a text box (named
txtExpendSubtotal) with an expression like:
=Sum(expenditure)

Then the main report text box can display what I think you
want by using an expression like:
=budgetsum - IIf([subreport control].Report.HasData,
[subreport control].Report.txtExpendSubtotal, 0)

 

[Melinda]

I am back on the main report and trying to bring the calculation from the
subreport to main report. I have an encumbered amount [sumofmoneyamount]
which is the total amount of money for that quarter, which is located on the
main report, on the subreport is a total of all the expenditures--I need to
subtract the [sumofmoneyamount]-the expenditures from the subreport and get a
total. I am not sure what I am doing wrong, but it will on subtract one
amount from the subreport from the amount of the main report. I am sure
this is quite confusing when you have no database in front of you. Sorry

Went back and tried again I got it to work, was using the wrong expression.
I will post back, I am going to the main report to try that. Thanks for all
your help.

I am trying to bring a subreport total, which is a calculated total, to the
main report. When it carries over to the main report it is not a sum of
figures. It only reflects the entry. I have a sum from the main report,
txtbudgetsum, which is correct, when I try to subtract the expenditures from
this it only picks up the first expenditure.

You need to calculate the sum in the subreport's Report
Header or Footer section using a text box (named
txtExpendSubtotal) with an expression like:
=Sum(expenditure)

Then the main report text box can display what I think you
want by using an expression like:
=budgetsum - IIf([subreport control].Report.HasData,
[subreport control].Report.txtExpendSubtotal, 0)

 

[Marshall Barton]

Use the expression I posted earlier using the name of the
subreport text box with the total (however it was
calculated).

I'm not sure, but it might be like:

=sumofmoneyamount - IIf([subreport control].Report.HasData,
[subreport control].Report.sumofmoneyamount, 0)
--
Marsh
MVP [MS Access]

I am back on the main report and trying to bring the calculation from the
subreport to main report. I have an encumbered amount [sumofmoneyamount]
which is the total amount of money for that quarter, which is located on the
main report, on the subreport is a total of all the expenditures--I need to
subtract the [sumofmoneyamount]-the expenditures from the subreport and get a
total. I am not sure what I am doing wrong, but it will on subtract one
amount from the subreport from the amount of the main report. I am sure
this is quite confusing when you have no database in front of you. Sorry

Went back and tried again I got it to work, was using the wrong expression.
I will post back, I am going to the main report to try that. Thanks for all
your help.

Melinda wrote:

I am trying to bring a subreport total, which is a calculated total, to the
main report. When it carries over to the main report it is not a sum of
figures. It only reflects the entry. I have a sum from the main report,
txtbudgetsum, which is correct, when I try to subtract the expenditures from
this it only picks up the first expenditure.


You need to calculate the sum in the subreport's Report
Header or Footer section using a text box (named
txtExpendSubtotal) with an expression like:
=Sum(expenditure)

Then the main report text box can display what I think you
want by using an expression like:
=budgetsum - IIf([subreport control].Report.HasData,
[subreport control].Report.txtExpendSubtotal, 0)

 

[Marshall Barton]

I think I pretty much have it ironed out with one little kink which I can't
figure out. I have three individual Spending Accounts, 4311, 4312, and 4313.
I bring in the subreport which is a break down of fund expenditures for
each Spending Account. So in a sense I am taking the Spending Account
beginning balance-the subreports fund expenditures so that we have an ending
balance of what they have to spend. When I run the report Spending Accounts
4312 and 4313 calculates correctly. The first spending Account 4311 does not
calculate out correctly. The beginning balance is $33,700.00 with fund
expenditures of $6,750.00, balance should be $26,950.00. The balance that
is reflected is $33,276.79. The difference is $1,423.21 which is my first
expenditure in SAC 4312. Hmmmmmmmmmmmm....
Here is what I have in my text box of the main report in the page footer.

=[txtbudgetsum]-Iif([subMelinda].Report![hasdata],nz([subMelinda].[Report.[grandbalance],0,0)

That expression has a ! where it should have a .
and the NZ is syntacticaly incorrect so I suspect that you
retyped the expression in your post. To save everyone's
time, use Copy/Paste and we won't have to waste time going
back and forth discussing posting typos.

I guess I am confused about how you are calculating the
total in the subreport. Your results suggest that the final
subreport total is not displayed in the subreport's Report
Footer section. At some point you said something about
using a running sum instead of Sum, if so make sure the main
report is referencing the subreport's report footer text box
and not the running sum text box.