Results 1 to 4 of 4
  1. #1
    dnelson is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    Oct 2018
    Posts
    2

    Sum() total in a textbox is adding up with strange decimal value. Should be zero.

    See attached Access Form. These amount should add up to zero. As you can see, the textbox shows a value of “.000000000000909” in the 1st screen shot. All my amounts were entered with 2 decimal places. Each amount is a debit (+) or acredit (-). I extended the places out to 15 so I could see if any amounts show out there in decimal point land. Nothing. All my other journal entries balance to zero and they work fine. My validation routine keeps stopping on this entry because it is not zero (as it should). Could there be a screwy bug in Access? What am I doing wrong? Any suggestions?


    I am not a pro, but I have developed many applications in access and other programming languages. Here is what Ihave tried:


    • Checked to make sure the “Amount” field in the table is number type set to “Double” with 2 decimal places.
    • I ran a total query on the table and I get the same value (“.000000000000909”) out side of the form.
    • I added it up manually and it adds to zero.
    • I tried rounding the “Amount” field to 2 places inside the Sum() function. No Joy! Here Is the formula: =Sum(IIf([Debit_Credit]="C",-(Round([Amount],2)),Round([Amount],2)))



    Attached Files Attached Files

  2. #2
    davegri's Avatar
    davegri is offline Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,388
    I suggest using Decimal and specify scale and precision properties in the table.
    Precision is the total number of digits allowed in a number, including left and right of the decimal.
    Scale is the number of digits allowed to the right of the decimal.


    So 1234567.89 has a precision of 9.
    123456.789 has a precision of 9 and a scale of 3.


    A precision of 4 and scale of 2 would result in a number with a maximum value of 99.99


    Percent designations like 125.5% would require precision 4 and scale of 3 (not one) since it is actually stored as 1.255.


    Precision has a maximum value of 28, but textboxes in forms allow only a max of about 15 decimal places.

  3. #3
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    or use the currency datatype. decimal precision is a universal problem, not limited to access, google to find out more

  4. #4
    dnelson is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    Oct 2018
    Posts
    2
    Thanks to all who assisted me on this. The solution was so simple I am embarrassed. All I needed to do was round the total on the sub form to 2 places. Thanks anyway. I learn something I may need someday about number precision.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 2
    Last Post: 11-16-2015, 08:13 AM
  2. Replies: 11
    Last Post: 06-09-2015, 10:32 AM
  3. Displaying a total textbox in a subform
    By rmohebian in forum Access
    Replies: 3
    Last Post: 02-08-2011, 01:05 PM
  4. Replies: 6
    Last Post: 03-17-2010, 10:09 PM
  5. total in textbox
    By micfly in forum Access
    Replies: 3
    Last Post: 11-09-2008, 11:24 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums