Exclude records from footer calculations but include in detail sec

  • Thread starter Midwestern Lori
  • 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
 
J

John Spencer

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top