Good morning,

I have a database in access that, among other things, records hours worked. From this field, with the date, I have a qry, or 2, that go through and add up all the hours for the employee for the past year. This then is subtracted from 1000, giving them a total of hours they have remaining. (as on calls, they can not work over 1040 hours in a rolling year, we cap it at 1000 so we know when they are close) This is then displayed in a report form so that the end user can see which on call has hours, so they may call them in to cover a shift.

That being said, one employee entered hours of 9.25. In the report it shows him having 9.350000000000002 hours remaining. He should have 9.35. If I drop the hours down to 8.25 entered it shows fine. If I put it at 9 hours it gives me the extra 000000000002 after the decimal. What can be causing this? I have tried to change the date, but there seems to be a threshold where the hours hit a certain amount and it adds the extra digits. I do have to say that he received a special 160 hours on top of this, so I have it in March as -160 and it has calculated fine until now.

Any ideas would be helpful

Thank you

That's a fact of life with floating-point calculations on computers. Just as certain numbers can't be represented exactly in base ten (think of, for example, 2/3), so too are there many numbers which can't be represented exactly in base two.

One approach is suggested in Currency: an underestimated Type in the Wiki, or there's an entire discipline (Numerical Methods) in Computer Science.

Ok changed it, but (thankfully it was on my test db) it took away all of the numbers after the decimal. It made the 9.25 into 9.00. and so on. I can not put the .25 back in, I am missing something I know.

I suspect you didn't change the field to Currency, but to Long Integer.

That is correct, number field, long integer in the design view.

The recommendation in the Wiki article I cited was to use Currency, not to use Long Integer.

ok, got it to work for entering new numbers, but it rounds all current entries to the nearest whole number. Anyway to prevent this?

Never heard of that happening before.

Try creating a new table with Currency fields instead of whatever the numeric fields are now, then write an Append query to populate the new table from the existing table.

BTW, another approach would be to store the times in minutes (in a Long Integer field), not in hours.

can you clarify this?

when you enter hours of 9:25, and have remaining hours of 9:35 - it implies you are working in date/time variables
ie, the two together add to 19 hours.

therefore where does the value of 9.3500000002 come from?

If you are not working in date/time variables, then you probably ought to be, let vba/access manage this for you.

I am not working in date time. It is now a currency field with a fixed number. I have hours entered in decimal form such as 9.25 = 9 hours 15 minutes. and 9.5 would be 9 and a half hours. The addition comes in when it is added all together for a year to determine the current hours of the employee in a floating year. The article posted above says why with a floating decimal the numbers add up wrong. But when I convert from text, to currency in the design field, it makes the 9.25 into 9. and earlier it makes 5.75 into a 6. I dont want it to round up or down when I convert.

Why was it text if you were trying to do arithmetic on it?

If it's text, definitely try what I recommended above (creating a new table with Currency fields instead of whatever the numeric fields are now, then write an Append query to populate the new table from the existing table). Use the CCur function in your Append query to assure that the text field is converted properly.

Thank you. I initially forgot to change it to number when I created the database, and when the information got put in and it seemed to be working, I didn't change it. I will try the append qry thank you.

It is working very good now except one problem. I had a worker who got a special agreement to exceed his hours by 160 for the year. I put this in initially as a -160 during that month. Problem is that now that its in currency, it will not calculate that as a negative. How to I get 160 hours taken off one individuals time? If I can alter the table data, how do I make it seen as a negative for the currency field.

Thank you

This post has been edited by wolvie13: Jun 4 2012, 09:59 AM

Currency fields can be negative. You don't need to treat them any differently than for any other number.

BTW. Currency is a scaled integer (AKA Decimal) datatype that uses fixed scale and precision to support calculations to four decimal places.

An alternative that allows up to 20 places (depending on the maximum number to be stored) use the more general Decimal type and set the Scale and Precision properties accordingly.