Sum function in Subreport

[MsJami]

Hi, I have a main report that has 5 subreports, each sub report has a total amount for that category. I have tried to sum up all the totals in the subreports to display it on the main report and it doesn't work. It is like access can't do that many numbers. or the box doesn't show or I get an error message.

I would be grateful for any suggestions on how to do this

Thanx
Jami

 

[dhookom]

What was the expression that you tried? Will all 5 subreports always have records? To get the value from a text box on one subreport, use:
=IIf(subrpt1.Report.HasData,subrpt1.Report.txtTotal,0)

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[Gavroche00]

Duane,

Can you clarifiy your posting. I am trying to get a calculated text box total from one sub report to the main report and it doesn't work. So let me first ask, can it be done? If so, I use a IIF function in the main report, is that correct?

Thank you

David

 

[dhookom]

Yes, it can be done. You only need the "IIf" if there is a possibility of the subreport not containing any records.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[Gavroche00]

Duane,

When I try to copy the calculated field to the main report, I get Access asking me to define my fields. How do I copy the cell to the main report?

For example, in my sub report I have txtTotal which is [gradea]+[gradeb]

When I insert an unboud text box in the main report header and write =txttotal, it doesn't take.

Thank you

David

 

[dhookom]

You can't just copy a control. You can reference a control like I suggested earlier:
=IIf(subrpt1.Report.HasData,subrpt1.Report.txtTotal,0)
txtTotal is a text box on the subreport.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[MsJami]

When I try to do the iif statement I get an error message #name?

I know some of my fields have no data.
here are just some of the fields I have
report.subcontracts w/text box sumofestimatedcost
report.equipment/rental w/text box sumofinvoiceamount
report.materialrecap w/text box sumamountspent
report.supplierinvoice1 w/text box sumamountspent
report.expenses w/text box sumamount
report.timebilled w/text box sumwage

this is basically what I have and and I want to add all the sums together and display in on the main report.

thank you
Jami

 

[dhookom]

What is your IIf() statement? Have you tried my earlier suggestion?

Give this a whirl:
=IIf(subcontracts.Report.HasData,subcontracts.Report!SumOfEstimatedCost,0) + IIf([equipment/rental].Report.HasData,[equipment/rental].Report!SumOfInvoiceAmount,0) + ...you get the idea...

I recommend avoiding punctuation, symbols, and spaces in object names.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[MsJami]

Does not work #error is the message.
I did it in expression builder
I am at my wits end
Jami

 

[dhookom]

As I asked in the public News Group... please post your full expression that you used. Also, is this text box in the same report section as the subreports?

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[MsJami]

Yes the text box is below in the same section
Here is the expression

=IIf(subcontracts.Report.HasData,subcontracts.Report!SumOfEstimatedCost,0)+IIf([equipment/rentals].Report.HasData,[equipment/rentals].Report!SumOfInvoiceAmount,0)+IIf([Materialrecap].report.hasdata,materialrecap.report!sumamountspent,0)+ IIf([supplierinvoice1].report.hasdata.supplierinvoice1.report!sumamountspent,0)+IIf[expenses}.report.hasdata,expenses.report!sumamount,0)+ IIf[timebilled].report.hasdata,timebilled.report!sumofwage,0)

This is what i did in the expression builder. Maybe it makes a difference that I created my reports based on queries. I don't know.

Thanks for working with me
Jami

 

[MsJami]

with the error report

=IIf(subcontracts.Report.HasData,subcontracts.Report!SumOfEstimatedCost,0) + IIf([equipment/rentals].Report.HasData,[equipment/rentals].Report!SumOfInvoiceAmount,0)+ IIf([materialrecap].report.hasdata,[materialrecap].report!sumamountspent,0) + IIf([supplierinvoice1].report.hasdata,[supplierinvoice1].report!sumamountspent,0) + IIf([expenses].report.hasdata,[expenses].report!sumount,0) + ([timebilled].report.hasdata,[timebilled].report!sumwage,0)

Thanks for being patient
Jami

 

[dhookom]

To troubleshoot, I divide and conquer. Try create a text box with just one subreport reference:
=IIf(subcontracts.Report.HasData,subcontracts.Report!SumOfEstimatedCost,0)
If this works then add the next...and next...

Keep in mind the subcontracts is the name of the subreport control, not necessarily the name of the subreport itself.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[MsJami]

One more thing my report is called subcontracts but the record source for that report is a query called subsrecap.
I don't know if that makes any difference.

Jami

 

[dhookom]

The record source of the subreport doesn't make any difference. Actually the name of the subreport doesn't make any difference. What makes a difference is the name of the subreport control on the main report. This usually matches the subreport name but not always.

I suggested you create a text box that references just one subreport. Did you try that? Did you have success? If you had success, did you try add more references to the expression?

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[MsJami]

Nope no success.
I even tried different subreports separtley and still nothing.

I am going to try something totaly different if it works I will let you know.

Thanks for your patience
Jami