Can't compare calculated numbers to imported numbers

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

When comparing a calculated field to an imported field, I get false deltas.
Premium x Commission Rate = Commission Amount. I then compare the
Commission Amount to an imported numeric value (the "expected" result).
The problem arises when Commission Rate is three decimals (0.055). It
doesn't seem to matter what I set the field properties to, Access is storing
the extra digits behind the scenes; so, when I subtract the calculated
Commission Amount from the imported "expected" result, I get fractions of
pennies (throwing off any queries I want to run, to identify matches vs.
deltas). I've even tried using the Round function, but that doesn't work
either. My calculation Update query using the Round function was this (Table
Name - "CM"):
UPDATE CM SET CM.CommAmt_Recalc = Round([Premium_Recalc]*[CommRate_Recalc],2);
But the calculated value was actually dropping the third decimal place
without rounding up!.. so a result of 19.185 is being saved as 19.18 insead
of 19.19.

Any help would be greatly appreciated ... even if the answer involves an
extra step or two!
 
Answering my own question ...

The best solution I can come up with is to let Access do its thing --
storing the fractions of pennies in the Commission Delta field (the result of
subtracting my calculated amount from the "expected" amount) -- and then
update the field with a hard "0" when the absolute delta is <.01. This will
turn all those unreal fractions into zeros, and I'll be able to run queries
against that field.

If anyone has a better idea, I'm definitely open to suggestions!

Thanks.
 
Back
Top