Results 1 to 6 of 6
  1. #1
    Join Date
    Jan 2003
    Location
    Rockville, MD
    Posts
    179

    Unanswered: Strange results when subtracting 205.18 from 211.16

    I'm getting strange results when subtracting 205.18 from 211.16. Here's what I did:

    1) Create a new .mdb
    2) Open VB (Ctrl-G).
    3) In the immedate window I enter this and press enter:
    ?211.16-205.18

    The results I get back are:
    5.97999999999999

    Anyone know what's going on? How can I get 5.98 instead of 5.97999999999999 when I subtract 205.18 from 211.16?

    Thanks,
    JT

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    looks like the traditional and much loved rounding error in floating datatypes inherent in virtually all Intel CPU's.
    ms access rounding error - Google Search

    forget the details but the correct choice of datatype is critical.. you may need double or decimal or currency.
    an alternative is to force the number of decimals using a rounding fucntion.. forget if its round, fix, floor or something similar
    another approach is to multiply by 100 fix it then divide by 100
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Jan 2003
    Location
    Rockville, MD
    Posts
    179
    Thanks for the reply healdem.

    I'm not sure the data type is the issue. In the immediate window, typing in just the values 211.16-205.18 (not variables), what would the default data type be?

    Also, I tried subtracting 205.18 from 211.16 in Excel and got 5.98. It was on the same machine, so it was using the same CPU. If the CPU was the culprit, wouldn't I see the same strangeness in Excel?

  4. #4
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    You have to tell Access how many places you want afer the decimal point.

    Round(211.16-205.18,2) will yield 5.98.
    Hope this helps!

    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  5. #5
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    round fixes the symptoms, but not the underlying problem
    Code:
    ?211.16-205.18
     5.97999999999999 
    ?cdbl(211.16)-cdbl(205.18)
     5.97999999999999 
    ?ccur(211.56)-ccur(205.18)
     6.38 
    ?cdec(211.56)-cdec(205.18)
     6.38 
    ?csng(211.56)-csng(205.18)
     6.380005
    ?cdec(211.16)-205.18
     5.98
    so cdec and ccur conversion functions do the job as required
    that means the decimal or currency datatypes are the right ones to pick.
    from that you should be using decimal unless its a currency column.

    I would expect that in the immediate window VB interprets numbers as double by default. the last example is probably interpreted by the immediate window as a decimal so whn you perfomr the subtraction it uses decimal for the 205.18

    as said before it s a well known glitch in the intel CPU. use of the correct datatype, in this case decimal will stop that being an issue. using the round function will work providing you assign that vlaue to another variable.

    one thing to bear in mind is if you are using these values on a report or from and rounding the values for display purposes, if the underlying datatype is wrong then totals will be wrong unless you apply the rounding consistently. using the correct datatype gets rid of such a need. of course if you want to run fingernails down a blackboard just to pee off accountants (no bad thing in my books)
    I'd rather be riding on the Tiger 800 or the Norton

  6. #6
    Join Date
    Jan 2003
    Location
    Rockville, MD
    Posts
    179
    Thanks again healdem.

    I did some digging around, and found a good explanation of the phenomenon.
    About floating point numbers and scaling in Access, Excel and VBA

    At first I was a bit suspicious because your cdec and ccur examples use slightly different numbers than my originals (so you got 6.38 instead of 5.98), but feeding the original numbers returns the expected 5.98:
    ?cdec(211.16)-cdec(205.18)
    5.98

    For my situation, looks like decimal types are the way to go. The numbers in my example have only two decimal place precision, but that's not always the case.

    Thanks again!
    -JT

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •