Exclude records from footer calculations but include in detail sec

  • Thread starter Thread starter Midwestern Lori
  • Start date Start date
M

Midwestern Lori

Would someone be able to help with this problem? I used the
=Sum(IIf([InclInAvg]="Yes",[SumOfHours],0)) (where [SumOfHours] is one of
about 20 fields I am summing) formula in each of five footers in my report
and that worked perfectly except for where I am looking at a field and
determining what color background it is to have on my report.

For instance, my code for the footer On Format [EventProcedure] is as follows:
Private Sub GroupFooter3_Format(Cancel As Integer, FormatCount As Integer)
If Me.Grouping = "Super" Then
If Me.[Text81] / [Text84] > 4.99 Then
Me.[Text92].BackColor = 65280
Else
Me.[Text92].BackColor = 12632256
End If
Else
If Me.[Text81] / [Text84] > 1.99 Then
Me.[Text92].BackColor = 65280
Else
Me.[Text92].BackColor = 12632256
End If
End If
End Sub

I have a slightly different one for each of my footers and my detail section.

But, when I put this formula in [Text81]:
=Sum(IIf([InclInAvg]="Yes",[SumOfTreatment_Count],0)), I get the following
error: Run-time error '6': Overflow. I've also gotten the error: Microsoft
Access can't find the field "\" (that should be an I-bar, but I couldn't
figure out how to type one) referred to in your expression.

In case it is helpful, [Text84]: =Sum(IIf([InclInAvg]="Yes",[# of Days],0)).
I didn't get any errors when I inserted that formula with the
[EventProcedure] for that footer.

I've really been struggling with this report. Unfortunately, I need to sum
the field, calculate an "average" and also determine the color on the field
with the average. Could someone please help me?

Thank you,

Lori
 
The error is probably caused by dividing by zero. If Text84 is zero, you
would get this error in the calculations.

Me.Text92.Backcolor = 12632256
If Me.Text84 <> 0 then
If Me.Grouping = "Super" Then
If Me.[Text81] / [Text84] > 4.99 Then
Me.[Text92].BackColor = 65280
End If
Else
If Me.[Text81] / [Text84] > 1.99 Then
Me.[Text92].BackColor = 65280
End If
End If 'Super
End If '<> 0




John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
 
Back
Top