?
Solved

Decimal problems during summation

Posted on 2002-06-07
4
Medium Priority
?
279 Views
Last Modified: 2009-06-04
I have a query that i am using in VB against Access.  The query sums the following number in a table:
0.002
0.0057
0.01

The result is 0.0177 but when I run the query in access I get this number: 0.01769999996759.  I realized that I can format the number but I do not understand WHY the query does not return just 0.0177.  The field is single type and I have tried double but have gotten the same result.  Is there anyway to get this to return the correct result everytime without have to format it and could someone please explain why I get this number with extended decimal places?
0
Comment
Question by:KGW22
4 Comments
 
LVL 4

Expert Comment

by:Glowman
ID: 7062381
Have you tried changing the field property "Decimal Places" in the table Design?  My guess is that it is set to Automatic.  Try setting it to 4 or 5.  Or if the resulting long decimal is being calculated in VB try using the round() function to round the result to 4 or 5 decimal places.  Hope this helps

G
0
 
LVL 44

Accepted Solution

by:
Arthur_Wood earned 200 total points
ID: 7062395
this should help you to understand what is going on "under the covers":

Intrernally, ALL numbers are stored as Binary values (even decimal fractions)

0.5 has an EXACT binary representation (0.5 decimal = 0.1 binary)

0.55 DOES NOT have an EXACT binary representation

Binary Fraction   Decimal Value
-------------------------------
.1                0.5       = 1/2
.01               0.25      = 1/4
.001              0.125     = 1/8
.0001             0.0625    = 1/16
.00001            0.03125   = 1/32
.000001           0.015625  = 1/64   and so on

on order to represent .05 in binary requires a NON-terminating series of
binary fraction values (.100011000011000011.... BINARY)


The real problem comes because binary arithmetic, with binary fractions,
is done as what is called 2-s complement, and there, the "round off", becuase
of the inexact representation of the decimal fractions, comes back to haunt
you.

This is not a BUG in the true sense of the word, but one of the shortcomings
of doing floating point arithmetic with a BINARY processor.  
0
 
LVL 8

Expert Comment

by:dovholuk
ID: 7062703
while arthur_wood is 150% correct on how a number is represented in decimal form, it won't help you any! (although is was a *GREAT* explanation)

another problem is how the data is getting stored in the database, BECAUSE of what arthur_wood is describing above.  when you defined the column in your table as single, and enter a value (such as 0.0057) access "did it's best" to put in 0.0057, but because of what was outlined above it failed. it is an either or situation. most programs take a floating point number and use a +/- on it to determine if it's "close enough" to the actual value. for instance, 15.000000000001 = 14.999999999999 which to many programs = 15 (exact). with floating point numbers (decimals) you can NEVER be sure your answer will always be 100% correct... would you agree arthur_wood? (or anyone else)

i think that arthur_wood deserves an a grade for his answer above.

cheers! :)

dovholuk
0
 
LVL 6

Expert Comment

by:blakeh1
ID: 7063457
Yes arthur_wood is correct. This is not just an access problem. That is why you have to be careful when checking floating point numbers for equality, escpecially in VB or VBA.
For example (142.857 + 714.286) = 857.143 will actually return false.

Here is some more info about how numbers are constructed from the binary

A floating-point number is stored in binary in three parts within a 65-bit range:
the sign, the exponent, and the mantissa.
 
   +----------------------------------------------------------------+
  | 1 Sign Bit | 11 Bit Exponent | 1 Implied Bit | 52 Bit Mantissa |
  +----------------------------------------------------------------+
 
The sign stores the sign of the number (positive or negative), the exponent
stores the power of 2 to which the number is raised or lowered (the
maximum/minimum power of 2 is +1,023 and -1,022), and the mantissa stores the
actual number. The finite storage area for the mantissa limits how close two
adjacent floating point numbers can be (that is, the precision).
 
The mantissa and the exponent are both stored as separate components. As a
result, the amount of precision possible may vary depending on the size of the
number (the mantissa) being manipulated. In the case of Excel, although Excel
can store numbers from 1.79769313486232E308 to 2.2250738585072E-308, it can only
do so within 15 digits of precision. This limitation is a direct result of
strictly following the IEEE 754 specification and is not a limitation of Excel.
This level of precision is found in other spreadsheet programs as well.
 
Floating-point numbers are represented in the following form, where exponent is
the binary exponent:
 
   X = Fraction * 2^(exponent - bias)
 
Fraction is the normalized fractional part of the number, normalized because the
exponent is adjusted so that the leading bit is always a 1. This way, it does
not have to be stored, and you get one more bit of precision. This is why there
is an implied bit. This is similar to scientific notation, where you manipulate
the exponent to have one digit to the left of the decimal point; except in
binary, you can always manipulate the exponent so that the first bit is a 1,
because there are only 1s and 0s.
 
Bias is the bias value used to avoid having to store negative exponents. The bias
for single-precision numbers is 127 and 1,023 (decimal) for double-precision
numbers.
0

Featured Post

Easily Design & Build Your Next Website

Squarespace’s all-in-one platform gives you everything you need to express yourself creatively online, whether it is with a domain, website, or online store. Get started with your free trial today, and when ready, take 10% off your first purchase with offer code 'EXPERTS'.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

What to do if a split doesn't fit? Or a bunch of invoice lines must be rounded while the sum must match a total? It takes a little, but - when done - it is extremely easy to implement.
Usually, rounding is performed by some power of 10 - to thousands, hundreds, tens, or integer - or to one, two, or more decimals. But rounding can also be done to a power of two, say, 16 or 64, or 1/32 or 1/1024, even for extreme values.
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…

589 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question