Last week a customer was really befuddled. He had a formula that said:
if {@field} = 0 then ...
He could see lots of zero values but the formula didn’t work as expected. He couldn’t figure it out so he sent it to me. The first thing I did was add a few more decimals to see if it was a rounding issue. That didn’t show anything, but the formula still insisted that the value was somehow NOT equal to zero.
So I went into the formula and multiplied the current value by one trillion. Then instead of zeros I started to see some small numbers. I am not an expert on floating point values or database precision but I have seen this before in reports. The solution is to round the value in the formula before comparing it to zero. In this case we rounded the value to two decimals like this:
if Round ({field}, 2) = 0 then ....
That made the formula behave as expected. The odd part is that I have seen the same problem with two other customers in the past week. It could be just a coincidence, but I figured I would mention this and see if this is happening to more people.
(For examples of my most popular formulas, please visit the FORMULAS page on my website.)