0

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)
Isaac Reefman
  • 537
  • 1
  • 8
  • 26

1 Answers1

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.

Erik A
  • 31,639
  • 12
  • 42
  • 67
  • Ok, so I'm planning to store it as a decimal *but* - is there a rounding function that avoids bankers rounding in Access, or would I have to make my own? – Isaac Reefman Jun 15 '18 at 07:54
  • Also, could I eliminate the need for that data macro simply by setting the field to a decimal with scale set at 2? – Isaac Reefman Jun 15 '18 at 07:55
  • `Format` in a data macro of course changes the data stored. For rounding functions, I tend to refer to [this page](http://allenbrowne.com/round.html) by Allen Browne, you might use the tricks described there, but you might also want to write your own. You can also view [this Q&A here](https://stackoverflow.com/q/137114/7296893), many good answers and prewritten rounding functions. – Erik A Jun 15 '18 at 08:00
  • Oh, and if you want to avoid additional floating-point errors when rounding, [Gustav's answer](https://stackoverflow.com/a/36966140/7296893) on that Q&A I linked to works with the decimal type to avoid that. Older answers tend to use doubles, because decimal is a relatively new type and more computationally intensive. – Erik A Jun 15 '18 at 08:05