Subtraction Giving an incorrect value

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

Guest

I have a control called PTO Carryover that returns 5.8. I also have an if
statement (named Carryover) that returns the same value, 5.8. The statement
is =Sum(IIf([PTO Type] Like "Carryover",[Paid Time Off],"0")). These values
are correct and I can see both of them on my report but when I subtract
Carryover from PTO Carryover (5.8 - 5.8) I get 1.907348635E-07.

Can anyone tell me what is happening? I've tried so many different ways to
get this to come out to 0 but I always get 1.907348635E-07.

Thanks in advance.
 
1.907348635E-07 is 1.907348635 time 10 to the minus 7th power (i.e.
0.0000001907348635). As you can see, this is almost zero. The problem is
that floating point numbers aren't handled perfectly, these tends to be a
round-off error. There are some data types that get handle this problem
better, but if you don't specify, the floating point numbers get treated as
Single or Double, which each have this problem. Currency and Decimal don't
because of the way they handle floating point numbers is different. You can
use the Currency data type even if the value isn't going to be currency, but
it does limit you to four decimal places. There is no Decimal data type for
VBA, so to use Decimal data types, you have to use Variants and the CDec()
function as you assign the value to the variant. The other option is to
round off the result to the number of decimals you need. In this case, if
you rounded to one decimal, the result would then be zero.
 
Hi Wayne,

Thanks for the information. I don't know what is happening because when I
ran the report today it was 0 but all of the other totals on the report had 4
- 7 decimal places. I ran it again and some of them had one and others 4 or
5. I think what I'm going to do is delete the data and enter again for this
individual since she is the only one that is having the problem. Thanks so
much for your help though. I'm not a programmer but I always learn something
new from those that reply.

Wayne Morgan said:
1.907348635E-07 is 1.907348635 time 10 to the minus 7th power (i.e.
0.0000001907348635). As you can see, this is almost zero. The problem is
that floating point numbers aren't handled perfectly, these tends to be a
round-off error. There are some data types that get handle this problem
better, but if you don't specify, the floating point numbers get treated as
Single or Double, which each have this problem. Currency and Decimal don't
because of the way they handle floating point numbers is different. You can
use the Currency data type even if the value isn't going to be currency, but
it does limit you to four decimal places. There is no Decimal data type for
VBA, so to use Decimal data types, you have to use Variants and the CDec()
function as you assign the value to the variant. The other option is to
round off the result to the number of decimals you need. In this case, if
you rounded to one decimal, the result would then be zero.

--
Wayne Morgan
MS Access MVP


Ann said:
I have a control called PTO Carryover that returns 5.8. I also have an if
statement (named Carryover) that returns the same value, 5.8. The
statement
is =Sum(IIf([PTO Type] Like "Carryover",[Paid Time Off],"0")). These
values
are correct and I can see both of them on my report but when I subtract
Carryover from PTO Carryover (5.8 - 5.8) I get 1.907348635E-07.

Can anyone tell me what is happening? I've tried so many different ways
to
get this to come out to 0 but I always get 1.907348635E-07.

Thanks in advance.
Click to expand...
Click to expand...
 
Back
Top