Adding totals in subreport to main report

I know this has been asked before and I've tried every one of the solutions.  So here I am.  I need to bring a sum from my subtotal into my main report.  This is what I tried (and failed):  =[Qry_Count_Unit_Awards_by_Unit subreport].[rpt_Award_Totals_by_Unit]![SumofAwards]

That is trying to just bring my sum field into the main report to use it.  The first field is my subreport, the second is my main report and the third is my sum field.  

Any suggestions welcome - and I'm an Access novice, so type slowly ;-)

- Sarah
KindirAsked:
Who is Participating?
 
peterpuscasCommented:
Use this for the control source:

=IIf( IsError( [Qry_Count_Unit_Awards_by_Unit subreport].Report.[textFieldFromSubreport])," ",=[Qry_Count_Unit_Awards_by_Unit subreport].Report.[textFieldFromSubreport])

Peter
0
 
ahmedbahgatCommented:
Hello Sarah

i do it this way :

1. in the main report make the Grand Total box non bund to any thing, make its name GrandTotal and is located in the report footer
2. in the sub report, assume the sub total box is named "SubTotal1"
3. in the main report assume the sub total box is named "SubTotal2"

4. use the OnFormat event for the main report footer section as follow:

me.GrandTotal= Me.SubTotal2 + Me.SubReport.Report!SubTotal1

cheers
0
 
peterpuscasCommented:
Just make the text field wich shows the SumOfAwards invisible on the subreport,than
use something like this on the main report:

=[Qry_Count_Unit_Awards_by_Unit subreport].Report.[textFieldFromSubreport]

you have to replace textFieldFromSubreport with the invisible field from subreport

Peter
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
KindirAuthor Commented:
Thanks, I have it working somewhat but when I put it in the main report it shows #error when there are no records.  When it was in the subreport it didn't do that.  Any idea how to get rid of the #error when there is no sum?  A zero would be fine, but not the error!

- Sarah
0
 
KindirAuthor Commented:
Thanks for your continued help Peter!  Now it's giving me invalid syntax - any idea what that could be?  

- Sarah
0
 
peterpuscasCommented:
You have to change [textFieldFromSubreport] with the name of your text field,
it has two appearences.

Peter
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.