Background color changes based on Output (1 Viewer)

[Dgavilanes]

Hi All,

I have a calculated field that reports in percentages Example: 341%.
Based on the output anything Higher than >100% should give a message "RED" with a RED background and <= "YELLOW", and =100% should be GREEN Color.

Hope it makes sense, anty help would be greatly appreciated

Thanks, Dennis

 

[pbaldy]

The simplest solution is Format/Conditional Formatting (on the ribbon beginning in 2007). The text you can do with an IIf() or Switch() function.

 

[Dgavilanes]

Hi Paul,

Thanks for your quick response, I was able to do the conditionl part of it, but the IIf() or Switch() function, have no clue

Thanks

Dennis

 

[pbaldy]

I believe Help has a pretty decent example of the Switch() function. Did you check there?

 

[Dgavilanes]

Im looking under help, but still not clear to me, I know is me

Thanks

Dennis

 

[pbaldy]

This type of thing:

Switch(FieldName > 1, "Red", FieldName > .5, "Yellow", FieldName > 0, "Green")

 

[Poppa Smurf]

I use the following code in a report that shows what account must be paid within the next 30 days.

If the due date of the account is within 30 days then the back ground of the text box is shown as Yellow, otherwise it is White.

Private Sub GroupHeader0_Format(Cancel As Integer, FormatCount As Integer)
Dim color_white As Long
Dim color_yellow As Long
color_white = RGB(255, 255, 255)
color_yellow = RGB(255, 255, 0)
If Me!due_date < Date + 30 Then
Me!due_date.BackColor = color_yellow
Else
Me!due_date.BackColor = color_white
End If
End Sub

 

[Dgavilanes]

Hi Poppa Smurf, Thanks for your reply, let me see if I can get this going.
My control source reads as follows =[1st Quarter (Actuals)]/[1st Quarter (Projected)] anf I wanto to get the background change and the word shoul match.

>100% "RED"
=<100 "YELLOW" etc.


Thanks again

Dennis

 

[pbaldy]

Poppa's code would only be appropriate for a form in single view; it will not work correctly in continuous or datasheet view. I would stick with Conditional Formatting.

 

[Poppa Smurf]

Paul

Sorry my mistake I forgot it was in the Forms and not the General forum

 

[Dgavilanes]

Hi pbaldy,

This is what I did and is almost perfect, the conditional part is not working I get the same color for everything and the condition are set correct as far I can see, I will keep trying,,,Thanks so much Dennis
=Switch([1st Quarter (Actuals)]/[1st Quarter (Projected)]>1,"Red",[1st Quarter (Actuals)]/[1st Quarter (Projected)]>0.5,"Yellow",[1st Quarter (Actuals)]/[1st Quarter (Projected)]>0,"Green")

 

[pbaldy]

Is it the formatting not working or the Switch() function?

 

[Dgavilanes]

Correct, is not working with Switch() function

 

[Poppa Smurf]

If the calculated field is an unbound field for a continuous or a datasheet view, then you will have the same colour for the calcuated field in each record.

 

[Dgavilanes]

The calculated field is an unbound field for a single form, is there way to do so with code instead of the conditional feature in access?
Please advise

Dennis

 

[pbaldy]

I just put assumed values in there; you'll need to adjust based on what the results of your calculation would actually be.

 

[Dgavilanes]

Thanks-Dennis

 

[Poppa Smurf]

Dennis

In the form code where you do the calculation add this code

Dim color_red As Long
Dim color_yellow As Long
Dim color_green As Long
color_red = RGB(255, 0, 0)
color_yellow = RGB(255, 255, 0)
color_green = RGB(0, 255, 0)

' calculations go here

' now format the backcolor
Select Case Me![name of the calculated field] e.g. me!calc_field
Case Is > 100
Me![name of the calculated field].BackColor = color_red

Case Is = 100
Me![name of the calculated field].BackColor = color_green

Case Is < 100
Me![name of the calculated field].BackColor = color_yellow

End Select

 

[Dgavilanes]

Hi Poppa Smurf,
Please forgive my luck of code writting, I'm still confused, If I send you a copy of what I have so far would that be OK?
Please let me know

Dennis

 

[Poppa Smurf]

Yes, that will be OK