How can I set conditional formatting on form to display different color of text dependng on value.

Question

Hi,

I am trying to set the font color  of some of fields on a subform to change depending on the value. I had seen a post somewhere that I need to use conditional format  on the fields I want to change. I've tried to do this on the first field which is Systolic

My target values are stored in a table named My Profile with the fields named in the format of TargetLoSystolic

My conditions are as shown in the screenshot:

I clicked enable on the first 2 rules which is why they appear to be greyed out but the first rule should be display Yellow text for the value, the second displays red for High value and I've changed third rule to display green for in range.

I've also tried expressiona similar to [Systolic] < [My Profile]![TargetLoSystolic]

From what I see, my font color does not change regardless of how I try and change the expression. I've also tried to enable the rule and also not click the enable button. I see no difference.

What am I doing wrong?

Answer 1

Syntax error. Access has no idea what you mean by [My Profile]![TargetLoSystolic]

Value < 100 would work.

Value < TargetLoSystolic would work too, *if* that field was on the form. Can you make it so?

If not, rather than "Field value is", choose "Expression is" and the expression is something like:

Systolic < DLoolup("TargetLoSystolic", "My Profile")

Btw, avoid spaces and other funny characters in object names. They may cause notational difficulties.

Answer 2

First, you don't have fields on a form. You have Controls that may or may not be bound to fields in a table. This is a subtle but important distinction

Second you can't reference fields in a table that way. Access will not know what record to pull the values from. You either need to include the fields on your form or use a DLookup to pull the values from the table.

Answer 3

Thanks Tom

I do not believe I can place the target level on the same form. I had it in the Profile table so I can enter the users target ranges, in this case me wife.

I tried to enter the expression as you suggested at least I think I did but it still shows the text on the Systolic control in black font color. Screen shot provided

My 3 expressions are as follows:

1. [Systolic]<DLoolup("TargetLoSystolic","My Profile")  (text should be Yellow in this case)
2. [Systolic]>DLoolup("TargetHiSystolic","My Profile") (Text should be Red)
3. [Systolic]>=DLoolup("TargetLoSystolic","My Profile") And [Systolic]<=DLoolup("TargethiSystolic","My Profile") (Text should be Green)

For my test records of that subform Systolic of record 1 should be yellow, record 2 green and record 3 green

Do I still have a syntax error?

Answer 4

First problem: since Systolic is left-aligned I assume this is a Text field. Bad idea. Blood pressure is expressed in mm of Hg, and thus is a number. Change to Number > Integer.

Answer 5

Second (minor) problem: You set the default of several fields to zero. Why? Null is MUCH better.