Math Error in Query??

  • Thread starter Thread starter el zorro
  • Start date Start date
E

el zorro

I have a query with a number field defined in the
associated table as "single." THe Query takes that number
and multiplies it by 4 (Expr1= [Field]*4). But something
strange happens when a number like 2.15 is entered into
the Field. Instead of getting 8.6 in Expr1, I get
8.60000038146973! (I'm using Access 2000.)

I can take the extra decimal places out of my reports
through formatting... but this worries me, like maybe
some dark danger lurks beneath it all. ANybody know
what's going on here? Is it because I tried to save on
bytes and defined the number as single instead of double?
 
el zorro said:
I have a query with a number field defined in the
associated table as "single." THe Query takes that number
and multiplies it by 4 (Expr1= [Field]*4). But something
strange happens when a number like 2.15 is entered into
the Field. Instead of getting 8.6 in Expr1, I get
8.60000038146973! (I'm using Access 2000.)

I can take the extra decimal places out of my reports
through formatting... but this worries me, like maybe
some dark danger lurks beneath it all. ANybody know
what's going on here? Is it because I tried to save on
bytes and defined the number as single instead of double?
Click to expand...

Yes. When you mix singles and doubles, strange things can happen.
The 4 is probably a double and if you've defined Field as single, there's
your trouble.

All versions of Microsoft BASIC, including Visual Basic for Applications
(which Access uses) have this "feature".

Tom Lake
 
Actually, the issue is not just BASIC.

Floating point numbers can store only a finite number of binary places,
similar to the fact that 1/3 can never be accurately represented as a
decimal number. The Single has fewer significant figures than the Double,
but both have the limitation, as do any floating point numbers in any
computer language.

If you need no more than 4 decimal places, you could use the Currency type.
It is a fixed point number (always 4 decimal places), and so is not subject
to the issues of floating point numbers. That's why it's used for Currency,
but it has other good uses as well.

There is also a Decimal field in Access 2000 and later. The Decimal is a
scaled number, like Currency but you can choose how many places you want.
Unfortunately, this field is very badly implemented, and Access cannot even
sort it correctly, so you are best not to use it. Details:
http://members.iinet.net.au/~allenbrowne/bug-08.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Tom Lake said:
el zorro said:
I have a query with a number field defined in the
associated table as "single." THe Query takes that number
and multiplies it by 4 (Expr1= [Field]*4). But something
strange happens when a number like 2.15 is entered into
the Field. Instead of getting 8.6 in Expr1, I get
8.60000038146973! (I'm using Access 2000.)

I can take the extra decimal places out of my reports
through formatting... but this worries me, like maybe
some dark danger lurks beneath it all. ANybody know
what's going on here? Is it because I tried to save on
bytes and defined the number as single instead of double?
Click to expand...

Yes. When you mix singles and doubles, strange things can happen.
The 4 is probably a double and if you've defined Field as single, there's
your trouble.

All versions of Microsoft BASIC, including Visual Basic for Applications
(which Access uses) have this "feature".

Tom Lake
Click to expand...
 
This has been dealt with before, here and elsewhere, almost ad
nauseam. Computers use a binary represention for real numbers. It is
not possible for a binary representation, however many digits it is
taken to, _precisely_ to represent a decimal fraction unless it
happens that that fraction can be expressed as the sum of a number of
negative powers of 2 (1/2, 1/4, 1/8). Most decimal fractions cannot
_precisely_ be expressed in that way, so the computer gets as close as
it can. When you enter 2.15, what you get is a binary fraction which
is "out" by some fraction of the value of the least significant binary
digit used to encode it. If you use a Double, rather than a Single,
the error will be much smaller, but it won't be zero. This sort of
minute error generally only matters if you are dealing with money
(!), when cumulative imprecisions can result in accounts failing to
balance. If this is your situation, you should consider using the
Currency or Decimal data types, which are "scaled" integers and do not
suffer from these imprecisions. The trade-off is that they can take a
(relatively) more restricted range of values than Integers, let alone
Reals.


I have a query with a number field defined in the
associated table as "single." THe Query takes that number
and multiplies it by 4 (Expr1= [Field]*4). But something
strange happens when a number like 2.15 is entered into
the Field. Instead of getting 8.6 in Expr1, I get
8.60000038146973! (I'm using Access 2000.)

I can take the extra decimal places out of my reports
through formatting... but this worries me, like maybe
some dark danger lurks beneath it all. ANybody know
what's going on here? Is it because I tried to save on
bytes and defined the number as single instead of double?
Click to expand...


Please respond to the Newsgroup, so that others may benefit from the exchange.
Peter R. Fletcher
 
Wow! Great responses! THanks everyone!
-----Original Message-----
This has been dealt with before, here and elsewhere, almost ad
nauseam. Computers use a binary represention for real numbers. It is
not possible for a binary representation, however many digits it is
taken to, _precisely_ to represent a decimal fraction unless it
happens that that fraction can be expressed as the sum of a number of
negative powers of 2 (1/2, 1/4, 1/8). Most decimal fractions cannot
_precisely_ be expressed in that way, so the computer gets as close as
it can. When you enter 2.15, what you get is a binary fraction which
is "out" by some fraction of the value of the least significant binary
digit used to encode it. If you use a Double, rather than a Single,
the error will be much smaller, but it won't be zero. This sort of
minute error generally only matters if you are dealing with money
(!), when cumulative imprecisions can result in accounts failing to
balance. If this is your situation, you should consider using the
Currency or Decimal data types, which are "scaled" integers and do not
suffer from these imprecisions. The trade-off is that they can take a
(relatively) more restricted range of values than Integers, let alone
Reals.


I have a query with a number field defined in the
associated table as "single." THe Query takes that number
and multiplies it by 4 (Expr1= [Field]*4). But something
strange happens when a number like 2.15 is entered into
the Field. Instead of getting 8.6 in Expr1, I get
8.60000038146973! (I'm using Access 2000.)

I can take the extra decimal places out of my reports
through formatting... but this worries me, like maybe
some dark danger lurks beneath it all. ANybody know
what's going on here? Is it because I tried to save on
bytes and defined the number as single instead of
Click to expand...
double?


Please respond to the Newsgroup, so that others may benefit from the exchange.
Peter R. Fletcher
.
Click to expand...
 
Back
Top