Miscalculating sum(table.field) in form txtbox

Question

I have a table tblTimeInLieu with a field HoursTaken containing these values: 1; 0.5; 2.25; 0.66

I then have a form frmTimeInLieu, Record Source: TimeInLieu, which contains a text box txtHoursUsed, Control Source: Sum([HoursTaken]) which returns this value: 4.41000003.

...

HOW!?

I've band-aided it by replacing the control source with FormatNumber(Sum([HoursTaken]), 2) so that it looks right for now, but I can't for the life of me work out what's going on.

---

Potential relevant information:

• It changed when I added the last value. Before then it was fine, always giving no more than 2 digits after the decimal point.
• The HoursTaken field contains data of type single with "Decimal Places" set to "Auto."

• I've added a Data Macro on the Before Change event of the Table that looks like this, but that's changed nothing. It was a bit of a long shot:

  SetField
      Name HoursTaken
      Value = FormatNumber([HoursTaken], 2)
  

Answer 1

Floating point number inaccuracy!

Since your column is a Single, which is the smallest floating number data type Access has, most operations that involves this column multiple times are bound to produce inaccurate results.

Either change your column type to Decimal, Currency, or use a rounding function to discard rounding data.

If you would change your column to a Double, the result would still be inaccurate, but with a smaller error.