Access conditional formatting

Question

I have a straight forward conditional format --- if Field1 not equal Field 2 print Field1 in red. It doesn't get much more basic than that and it does what it's supposed to do... most of the time.  For some reason, every once in a while it will print in red even though the number are equal. Field2 is a calculated field so I tried Field1 not equal (Part1 + Part2 + Part3) again - works most of the time but the same errors. I thought maybe it was a rounding error so I made sure all my fields were formatted standard and set to 2 decimals. Same thing. I tried formating them out to 10 decimal places and they were equal but it printed red. I tried coming at it from the other direction and setting everythng to print in red then said print in black if equal. That gave me the same errors. I even tried removing the conditional formatting altogether and using VBA to set the forecolor. That gave me the same errors as well. It is always the same records that print wrong no matter how I approach it. What am I missing here? Is there a bug in Access that I don't know about? I'm still using 2003 (SP3). Any ideas?

TIA. Lynne

Answer 1

How about this:

Expression Is;  CLng([Field1]*100) <> CLng([Field2]*100)

Answer 2

That works! You are brilliant! Thanks so much!

Answer 3

I tried this as well as the suggestion made by John Spencer. I still get records printing in red when the amounts are equal. It is always the same records that print incorrectly whether I use the conditional formatting or VBA. I have gone back to my raw data and confirmed the amounts are equal. Any other ideas? This is such a simple thing to be spending so much time on but it is driving me crazy.

Answer 4

Or change the conditional expression to

Expression Is;  Abs([Field1]-[Field2]) < .0001

Answer 5

Formatting and rounding are different things. Formatting doesn't change the value. So if the values are not the same to the last decimal place it will show as Red.

Change your expression in the Conditional Form to:

Round(Field1,2) <> Round(Field2,2)