Results 1 to 15 of 15
  1. #1
    Join Date
    Oct 2008
    Posts
    3

    Angry Unanswered: Difference of two same value is coming in Scientific notetion.

    I am finding a difference of two double value which are same but instead of zero it returns Scientific notation.
    i.e.

    1,484.14 - 1,484.14 = 2.27373675443232E-13

    Why?.


    Alok.
    Reply With Quote

  2. #2
    Join Date
    Feb 2004
    Posts
    214

    It is possible

    that the numbers you are finding the difference from are formatted to display just 2 decimal places, but it actually holds the entire value. If you want 2 decimal places only, make sure it is set up that way in your table and not formatted that way in your query.
    You know, I'm sick of following my dreams, man. I'm just going to ask where they're going and hook up with 'em later

  3. #3
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    The issue is actually to do with the datatype you're using.
    Doubles are not precise numbers and as such you get floating point errors as you're seeing.

    Looks to me like you should be using a decimal datatype (exact) instead.

    P.S. 2.27373675443232E-13 is horrendously close to zero, just not quite zero
    George
    Home | Blog

  4. #4
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    0.000000000000027373675443232 or there abouts anyway
    George
    Home | Blog

  5. #5
    Join Date
    Feb 2004
    Posts
    214

    ...

    I have never had success with using decimal.
    When I try it, the decimal values do not show up in the table.

    When I set up the table as a double, format standard to 2 decimal places and subtract 1,484.14-1,484.14 I get zero.
    You know, I'm sick of following my dreams, man. I'm just going to ask where they're going and hook up with 'em later

  6. #6
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Quote Originally Posted by mr. blonde
    When I set up the table as a double, format standard to 2 decimal places and subtract 1,484.14-1,484.14 I get zero.
    No you don't.

    You've formatted it to make it look like zero, but it's not
    George
    Home | Blog

  7. #7
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Yup, just formatting the numbers usual solves most problems. If you get paranoid about it, then you have to make a lot of use of the Round() function.
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  8. #8
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    It doesn't solve the problem, merely hide it.
    You wouldn't get away with this in a financial system, that's for sure!
    George
    Home | Blog

  9. #9
    Join Date
    Feb 2004
    Posts
    214

    ...

    georgev,

    I see my error on using Decimal. I set the decimals to 2 but I needed to change my scale.

    I agree that the double is not 100% exact, but is there any issue using it over the decimal? I have used it for years over using decimal and have never run into a conflict but I only go 2-4 decimals places out. Is the issue with Double vs. Decimal only when performing math calculations going several decimals places out? If someone is only going 1-4 decimals places out, would it still be best to use double? I know space is not really an issue anymore like it use to, but I usually use the smallest format what I am working with.
    You know, I'm sick of following my dreams, man. I'm just going to ask where they're going and hook up with 'em later

  10. #10
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Float isn't a precision datatype.

    Float is represented internally as a binary number, so there are some binary fractions that have no exact decimal representation.
    George
    Home | Blog

  11. #11
    Join Date
    Feb 2004
    Posts
    214

    ...

    I'm just curious though if I need to change my tables to decimal? Currently I don't have any open project going past 2 decimal places. Should I convert them over to decimal, or do you think it is ok as double since my level of precision is low with my 2 decimal places?
    You know, I'm sick of following my dreams, man. I'm just going to ask where they're going and hook up with 'em later

  12. #12
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    The real question is; what are you storing in this field?
    George
    Home | Blog

  13. #13
    Join Date
    Oct 2008
    Posts
    3
    Quote Originally Posted by georgev
    The real question is; what are you storing in this field?

    georgev,

    I am make a account statement of a bank account. In this account there are more then one records are having decimal value and most importantly sum of receivings and payments should be equal. But in my case it is differing with 0.03 part of unit.

    Should I convert all entry into text and get val() of that values , will it work?


    Alok.
    Last edited by alokshri67; 11-02-08 at 09:59.

  14. #14
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    if you don't need more than four figures after the decimal point, use Currency datatype - it is a scaled integer and absolutely precise to four places. the scaling/descaling is handled automatically by Access.

    text is a pointless option when you have the explicitly purposed Currency datatype.

    where possible avoid the Decimal datatype - it is poorly implemented and will complicate your life. unless you need to go beyond four figures after the decimal point, the Decimal datatype offers lots of pain but zero gain.

    do not use floats (Double, Single) for anything to do with accounting.

    izy
    Last edited by izyrider; 11-02-08 at 11:20.
    currently using SS 2008R2

  15. #15
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Agreed with izy.
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

Posting Permissions

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