Totaling calculated report fields in Access 2003

[bjs626]

On a custom report, there is details data (subreport). If the subreport value is null, the details data does not populate.
I need to sum the the totals of 5 separate sub reports. I have been able to get subtotals of zero for the ones that are null by using a calculated textbox. The problem is that I cannot sum these calculated textboxes. When I run the report, it asks me to enter parameters on all of the calculated textboxes.

Thanks in advance for any help.
bjs626

 

[sxschech]

Would this link help in your situation?

Bring the total from a subreport back onto the main report

http://allenbrowne.com/casu-18.html

 

[bjs626]

Hi,

This is a great link, but I figured out to use the Iif statement to pull the information into the custom report. My problem now is to get the calculated fields to sum.

So I have.

Iif statement result
Iif statement result
Iif statment result

I am now trying to calculate the total result of those 3 calculated fields. ACCESS says I need to use the above formulas in the Sum statement but that is not working.

Thank you for your help.

 

[sxschech]

Perhaps you could post the entire forumula. I pasted a textbox formula from one of my reports, this will not solve your problem, only to illustrate what you should copy and paste to see what your calculation looks like.

=IIf(Max(Left([FallTerm],4))=Left([FallTerm],4) Or Max(Left([FallTerm],4))-1=Left([FallTerm],4),Null,[Yr1Attrition]+[Yr2Attrition])

 

[chainedtodesk]

based on your IF statement theory wouldnt something like this get you what you need for a solution.

Dim sub1, sub2, sub3, sub4, sub5, ttlSUB As Variant

If (Not IsNull(Me.Text1)) Then sub1 = Me.Text1 Else sub1 = 0
If (Not IsNull(Me.Text3)) Then sub2 = Me.Text3 Else sub2 = 0
If (Not IsNull(Me.Text5)) Then sub3 = Me.Text5 Else sub3 = 0
If (Not IsNull(Me.Text7)) Then sub4 = Me.Text7 Else sub4 = 0
If (Not IsNull(Me.Text9)) Then sub5 = Me.Text9 Else sub5 = 0

Me.ttlSUB = (sub1 + sub2 + sub3 + sub4 + sub5)

 

[dhookom]

It isn't clear whether the subreports are returning any related records or not. If the issue involves no data in the the subreport, you can generally use an expression like:

=IIf([subreportA].Report.HasData,[subreportA].Report.txtMyTextBox, 0)

Duane
Hook'D on Access
MS Access MVP