Conditional Formatting on calculated control

[Stanski21]

:banghead::banghead::banghead::banghead::banghead::banghead::banghead:

I have a report with 7 calculated fields. Conditional formatting through the Conditional Formatting Rules Manager work wonderfully on 5 of them.
The last 2 are formatted on an expression based on an OnLoad calculation based on the dates of the report, and will not colour correctly (if at all). I have tried everything (except the correct way apparently)!

The formatting calculation is supposed to trigger relative to the % of the way through the month. If the value of performance is higher than the % of the way through the month, it's green; yellow for >value*0.8 and red for value<*0.8.

I have tried running the date calculation through the conditional formatting manager, through VBA, on the report control directly - as well as creating an 'invisible' copy of the value and basing the formatting rules on that.

The only thing I can think of is that these last two fields have needed to be explicitly expressed as a "0.0%" format within the SQL on the powering query because of the complicated calculations they are based on... would that affect it?

 

[Minty]

It is probably to do with the format, could you include an un-formatted hidden field with your value in it and base the conditional formatting on that.

 

[Stanski21]

Yeah I tried that. It's still formatting all fields as if the expression is true regardless of the value

 

[Stanski21]

D'oh! I've just noticed that the fields are being displayed as Text not Numbers... That explains why the COnditional Formatting isn't working... now I need to figure out why...

 

[Minty]

If you are using Format() it will return a string.

 

[Stanski21]

Oh really? That's interesting and good to know. I can fix that.
But the conversion from number to string seems to happen as soon as I calculate against the datediff calculation on the query behind the query that powers the report...
I might have to redesign. Grrr

 

[Minty]

If you perform the final display format on the form I suspect you can maybe remove some of the earlier query based formatting ?

 

[Stanski21]

Thank you for your help so far!

If you perform the final display format on the form I suspect you can maybe remove some of the earlier query based formatting ?

Tried that too... removing all Format() along the way still displays the field as text... so I can't format it on the report that way. I've even gone as far as changing the table field from currency to number to see if the division of currency by a Long (as a result of the DateDiff) made a difference... nope nope and nope.

 

[Minty]

I've struggled with CF the past like this - Create a couple of fields in your query called tagRed, tagYellow and simply do an tagRed : IIf (Value> x , 1, 0) then use those fields to do the CF on the form.

 

[Stanski21]

..... It was Nz()... :banghead:
Nz().....

How it has betrayed me...

Oh well. Thank you for all your help!

 

[npatel]

Thank you for sharing...