Change Background Color based on two field values, and with multiple conditions

Question

Change Background Color based on two field values, and with multiple conditions

jpburgundy

Grasshopper

Points: 24
More actions
June 2, 2012 at 10:17 pm

#274980

I have a report need to change one field background color based on another field data, and value at the field self.
E.G.
Category Number
Apply 100
Orange 200
Pear 150
if category column shows Apply, then
when Number > 100, Number field backgroud color is Black
Number >50 and < 100, Number field background color change to Green.
When Number <50, Number field background color change to Red.
if Category column shows Orange, then
when number > 50, Number field background color is Black
Number <50 and >20, color is Green
Number <20 then Number field background color is Red
If Cagegory Column shows Pear, then
If number >=0 and < 30, color is green,
if number < 0, then Red
else, color is Balk.
The code I wrote as below:
Select Case Fields!Category.Value
Case "Apply"
If (Field!Number.value >= 20 and (Field!Number.value) < 50 Then
Return " Green"
ElseIf (Field!Number.value ) <20 then
Return "Red"
Else
Return "Black"
End If
Case "Orange"
If (Field!Number.value) >=0 and (Field!Number.value) <=30
Then
Return "Green"
Elseif(Field!Number.value) < 0 then
Return "Red"
Else
Return "Black"
End If
Case "Pear"
If (Field!Number.value) >= 0 and (Field!Number.value)<= 10 then
Return "Green"
ElseIf(Field!Number.value) < 0 Then
Return "Red"
Else
Return "Black"
End If
End Select
I put this code at a Calculate field, then at background color property, refer to the Calculate field, it didn't show any error, but color didn't change.
Then I tried to put this code at the number column Background color property directly, it showed error message as Invilid.
Would someone give me suggestions how to do that?
I searched online, didn't see anything silimar as my questons.
I am much appreciated if someone can help me out.
Thanks a lot.

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Rock from VbCity Hall of Fame Points: 3991 More actions · Answer 1

Hi jpburgundy

The explanation of your issue does not correspond with the code you posted, the ranges are different, anyway, I tried with one of my reports to do something similar, below is the code for my function SetColor

Public Function SetColor(ByVal p1 as String, ByVal p2 as integer) As String

'// p1 possible values are F = Fail, R = Running, S = Success'

Select Case P1

Case "F"

If p2 > 3000 then return "red"

If p2 > 2000 then return "orange"

return "Tomato"

Case "R"

if p2 > 3000 then return "green"

if p2 > 2000 then return "blue"

return "cyan"

Case "S"

if P2 > 3000 then return "gold"

if p2 > 2000 then return "khaki"

return "yellow"

End Select

Return "black"

End Function

It takes two parameters, p1 and p2, p1 receives a string, its possible values are 'F', 'S' and 'R', while the second parameter (p2) receives a time in milliseconds.

Then on the report a reference this function at one of its fields is added to its Fill property, as shown in the image below.

With the following end-result

Cheers,

Hope this helps,
Rock from VbCity

jj1234hu Newbie Points: 8 More actions · Answer 2

Hi, Rock from Vb City,

Thank you so much for your help. Your post helped solve my issues.

I really appreciate you kindly sharing your advice and your time.

Thanks again!

jpburgundy.