Results 1 to 3 of 3
  1. #1
    Join Date
    Dec 2007

    Question Unanswered: Query returns 2.2723E-13 instead of 0

    I've got the following statment in a query:
    RunningBalance: (SELECT SUM([TOTAL]) FROM Acctranquery x WHERE ACCTRANquery.ACC_CODE = x.ACC_CODE AND Acctranquery.NUMBER >= x.NUMBER)

    Which works great EXCEPT for when the RunningBalance = 0
    Whereever the running balance reaches 0.00 I get something like 2.2737E-13 or 1.9895E-13. Similally any sums I try to do using the running balance column which should end up giving me 0.00 return a similar result. This is a problem since I use running balance to display the starting balance in invoices. Customers who feel they were paid up to date my be worried by a balance of 2.2737E-13 carried forward!

    Anyone have any thoughts on what this is or how to stop it?


  2. #2
    Join Date
    Nov 2007
    Adelaide, South Australia
    Round it to two decimal places and the problem will disappear.
    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

  3. #3
    Join Date
    Dec 2002
    Préverenges, Switzerland
    you are victim of floating-point imprecision.

    1.10 *cannot* be represented precisely in binary
    1.20 also.
    there is an infinte set of "every-day" numbers that cannot be represented precisely in binary.

    you should not use double/single datatypes to represent monetary amounts. here is a simple demo

    use the currency datatype.
    access internally multiplies these numbers by 10000 and stores/manipulates currency data as integer. the currency datatype is able to handle numbers to the fourth decimal place with absolute precision.

    'Trekker's rounding is a possible workaround but eventually fails when the numbers get very large or you have many numbers or you multiply by a sufficiently large value.

    currently using SS 2008R2

Posting Permissions

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