change color in a control depending on value

[DrDoIT]

Hello Forum,
I would like to change the text color in a field depending on the value in the field called Results. The code I have entered is as below.

Private Sub Report_Load()
If [Results] = "POSITIVE" Then
[Results].ForeColor = RGB(255, 0, 0)
Else
[Results].ForeColor = RGB(0, 0, 255)
End If
End Sub

When I enter similar code for fields with number as the data type, it works well. However for fields with text as the data types, all the results take the color specified in the 'Else' part.

Are Text data types supposed to be handled differently and how do i do this?

Thank you,

DrDoIT

 

[pr2-eugin]

DrDoIt, have you check using Conditional Formatting? Simpler, without any coding..

 

[missinglinq]

...all the results take the color specified in the 'Else' part...

I suspect that this is because the Value of Results, in the first record displayed, is not "POSITIVE." With this code in the OnLoad event, the Control is going to be formatted based on the Value of it in the first Record being displayed. Since you're doing nothing to change this formatting, going from one Record to another, the Control in all Records remains the same as in the first Record.

As pr2-eugin said, use Conditional Formatting. In Report Design View:

1. Right-Click on the Control
2. Click on Conditional Formatting
3. Under Condition1 select "Expression Is"
4. In the next box enter [Results] = "POSITIVE" (exactly like that, including the Square Brackets)
5. Select the Formatting you want
6. Click on OK

Linq ;0)>

 

[DrDoIT]

thanks, this worked great.
DrDoIT