Background Color in a Report

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a report that has an unbound text box that changes background color
depending on the results of a calculation.

The textbox takes an average of a number of calculations from another bound
text box.

Private Sub ReportFooter_Format(Cancel As Integer, FormatCount As Integer)
If Pungency < 3.545 Then
Me.Pungency.BackColor = 16711680
ElseIf Pungency < 5.245 Then
Me.Pungency.BackColor = 65280
ElseIf Pungency >= 5.245 Then
Me.Pungency.BackColor = 255
End If

End Sub

On a report that I am trying to calculate the average equals 5.224222222 and
rounded to 5.2

The background color is showing the 255 which is red but it should be 65280
which is green.

I would like to know what am I doing wrong in the Private Sub routine that
is making the background color wrong.
 
Curt,
I ran your code, but didn't get the results you did.
Given a Pungency value of 5.224222222 or 5.2... I got a green
background.
According to the code you posted... that makes sense. If it's a report,
and there a re a series on Pungency values, perhaps a previous Pungency
value "set" the color, and that kept going right into this example.

The problem I see with your code is that a Pungency of 2 staisfies the
first 2 IFs, so the 5.245 (green)... being last... wins out.

I think you want this...
If Pungency < 3.545 Then
Me.Pungency.BackColor = 16711680
ElseIf Pungency >= 3.545 AND Pungency < 5.245 Then
Me.Pungency.BackColor = 65280
ElseIf Pungency >= 5.245 Then
Me.Pungency.BackColor = 255
End If
Click to expand...

And... while you're testing, carry Pungency out to 3 places just like
your criteria values. (just to be on the safe side)

Al Camp
 
I made the corrections as you suggested but it still does not make the
backcolor green.
I have included the whole Private Sub routine, maybe this will make sense.
Another problem with this is when the backcolor should be green with a 3.6
but it makes the backcolor the default color of white.
The calculation to get the pungency amount is an average of 1 to infinity
pungency amounts.

Private Sub ReportFooter_Format(Cancel As Integer, FormatCount As Integer)
If Pungency < 3.545 Then
Me.Pungency.BackColor = 16711680
ElseIf Pungency >= 3.545 And Pungency < 5.245 Then
Me.Pungency.BackColor = 65280
ElseIf Pungency >= 5.245 Then
Me.Pungency.BackColor = 255
End If

End Sub
Any suggestions.
 
Is Pungency in the Report Footer Section?
Have you tried setting a break point to step through the code?
Is the control background set to Transparent?
 
Hello Duane,
The average is calculated in the Report Footer Section but the actual
Pungencies are in the Detail section of the Report.
The control background is set to Transparent.
I have not tried setting a break point to step through the code. I will try
and see what happens.
 
I don't get it. your code is in the report footer but the only control/field
you reference is in the detail section. Plus, you should try set the back
style to white so it isn't transparent.
 
Back
Top