Background Color in a Report

[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.

 

[Al Camp]

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

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

 

[Guest]

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.

 

[Duane Hookom]

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?

 

[Guest]

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.

 

[Duane Hookom]

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.