Changing color in IIf statement?

[twitchie]

I have this expression:

=IIf([Text131] Between 16 And 30,"Unacceptable",IIf([Text131] Between 31 And 42,"Marginal",IIf([Text131] Between 43 And 56,"Effective",IIf([Text131] Between 57 And 71,"Very Good",IIf([Text131] Between 72 And 80,"Outstanding","")))))

It works just fine, but I was wondering if there'd be a way to change the text color of the <<true>> statement based on what it is? i.e. "Outstanding" and "Very Good" would be green, "Effective" could be yellow, "Marginal could be brown and "Unacceptable" could be red. How would I implement that into this expression, if that's even possible? Thanx for your assistance!

 

[Moniker]

You can't change the color in an IIf statement -- you have to hit the property of the textbox (or whatever control) you want to change the color on. Also, consider using a switch function instead of all those nested IIf statements, which are very hard to read/maintain. As an alternative, this would work:

    Select Case Text131
        Case 16 To 30
            Your_txtBox_Name.ForeColor = vbRed
            Your_txtBox_Name = "Unacceptable"
        Case 31 To 42
            Your_txtBox_Name.ForeColor = vbBrown
            Your_txtBox_Name = "Marginal"
        Case 43 To 56
            Your_txtBox_Name.ForeColor = vbYellow
            Your_txtBox_Name = "Effective"
        Case 57 To 71
            Your_txtBox_Name.ForeColor = vbGreen
            Your_txtBox_Name = "Very Good"
        Case 72 To 80
            Your_txtBox_Name.ForeColor = vbGreen
            Your_txtBox_Name = "Outstanding"
        Case Else
            Your_txtBox_Name = ""
    End Select

 

[twitchie]

I got rid of the nested IIf statements and added this code (changing the text box names to mine) to the Before Update, After Update and On Change property fields of the box, but not only does the text color not change, but the original effect of changing text displayed based on score doesn't work. Obviously I'm doing something wrong here. Any ideas? After removing the IIf statement, the text box says it is Unbound in design view if that helps. Thanx!

 

[KKilfoil]

As long as you only need four or fewer different colors, you can use 'conditional formatting' (CF) on all Access versions after 2000.

Just write expressions for each of your cases, with the default color being one of your choices. So, you format the text in the control to be green in the usual manner, apply a separate CF for each of the cases that are not 'outstanding' or 'very good'.

This is more limited than Moniker's code, but easier to implement.

CF can be found under 'Format' in the form design window.