Problem summing numbers in the footer section of a report

I have a main report with 3 subreports.  In the footer section of the main report I am trying to sum values in the 3 subreports.

Here is my control source for the summed field:


But the result I get is...


if only one of the subreport fields has a value and the other two subreports have no value because there are no records in them.

Who is Participating?
Jeffrey CoachmanConnect With a Mentor MIS LiasonCommented:


Then try something like this syntax:

Nz(Dsum("CostCtrGroupRegHrsTotal","subrptTimeCardDataEntryRECORDSOURCE", "EmpName Like " &  "*" & [Forms]![frmTimeCardDataEntry]![txtName] & "*" & " AND " & "TranxDate Between " & [Forms]![frmTimeCardDataEntry]![txtStart] & " AND " & [Forms]![frmTimeCardDataEntry]![txtEnd]))

Jeffrey CoachmanMIS LiasonCommented:
Don't sum the subreports, ...instead try summing the fields in the subreport's recordsource:


Or something roughly like this for your object names:

...Here you may not even need the NZ(), ...but keep it in for now, just to test...

SteveL13Author Commented:

This works but gets the sum total of all records.


I need to add two criteria...  

EmpName Like "*" & [Forms]![frmTimeCardDataEntry]![txtName] & "*"
TranxDate Between [Forms]![frmTimeCardDataEntry]![txtStart] And [Forms]![frmTimeCardDataEntry]![txtEnd]

I tried this but it must not be right:

=Nz(Dsum("OTHrs","qryTimeCardDataEntry"))WHERE [EmpName] Like "*" & [Forms]![frmTimeCardDataEntry]![txtName] & "*" AND TranxDate Between [Forms]![frmTimeCardDataEntry]![txtStart] And [Forms]![frmTimeCardDataEntry]![txtEnd]+Nz(Dsum("OTHrs","qryTimeCardDataEntryCostCtrs"))WHERE [EmpName] Like "*" & [Forms]![frmTimeCardDataEntry]![txtName] & "*" AND TranxDate Between [Forms]![frmTimeCardDataEntry]![txtStart] And [Forms]![frmTimeCardDataEntry]![txtEnd]+Nz(Dsum("OTHrs","qryTimeCardDataEntryCostCtrsGrp3"))WHERE [EmpName] Like "*" & [Forms]![frmTimeCardDataEntry]![txtName] & "*" AND TranxDate Between [Forms]![frmTimeCardDataEntry]![txtStart] And [Forms]![frmTimeCardDataEntry]![txtEnd]
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.