Results 1 to 7 of 7
  1. #1
    f15e is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Mar 2016
    Posts
    71

    Floating Point Errors in my Report

    I am having a lot of trouble trying to get floating points values correct. My numbers are to the third decimal point. Some come out correct and while others do not. I've tested them in Excel and it comes out correctly but not when doing calcs within Access. For example, I should get a value of 744.744 but instead I get 744.740. Very frustrating. I need to have the 3rd decimal showing the correct value.



    I would appreciate if someone provided me why this is happening and how to fix it.

  2. #2
    andy49's Avatar
    andy49 is offline VIP
    Windows 10 Access 2007
    Join Date
    Nov 2016
    Location
    London
    Posts
    1,051
    Sounds like you'll need

    http://allenbrowne.com/round.html


    Sent from my iPhone using Tapatalk

  3. #3
    davegri's Avatar
    davegri is online now Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,389
    Try changing the numbers to decimal and adjust the precision and decimal properties.
    A precision of 2 means that a decimal number can be no greater than 2 digits. (44 or 4.4 or .44)
    A scale of 3 indicates 3 decimal places.
    Try changing your precision to something like 8, or whatever the max number of digits including the decimal in the sums might not exceed.

  4. #4
    f15e is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Mar 2016
    Posts
    71
    I'll give that a try. Thanks for the help!

  5. #5
    f15e is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Mar 2016
    Posts
    71
    This is driving me nuts!! First I'd like to know why Access has a Round() function that is based on Banker's Rounding? Why not use the typical rounding of .4 or lower round down and .5 or higher, round up and if Banker's Rounding is needed, just use a built-in function? In this case, it seems like Banker's Rounding is the default and if you want to use the standard way of rounding, you have to create your own function or manipulate your calculations. I understand the point of Banker's Rounding to even out the bias toward the upper end of the scale but in my case it's not working very well.

    For example, I want to calculate the weighted average of elevations (in ft.), from my table, that fit a given criteria. When I run the calc in MS Excel, I get 30.669 which is correct. When I run it in access, I get 30.987. I understand may seem like a very small difference between the numbers, but when you to be accurate with your results, this doesn't cut it. I've tried using Round(), without Round(), Format(), CDec, using Ken Getz' custom rounding function, and several other ways and have not gotten the results I expect. It seems that the more summing iterations required, the further off the result is from the actual value. Does anyone have any advice or other methods to try? I greatly appreciate any help you guys could provide. Thank you.

  6. #6
    andy49's Avatar
    andy49 is offline VIP
    Windows 10 Access 2007
    Join Date
    Nov 2016
    Location
    London
    Posts
    1,051
    What values are you using and calculation to get those values


    Sent from my iPhone using Tapatalk

  7. #7
    John_G is offline VIP
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2011
    Location
    Ottawa, ON (area)
    Posts
    2,615
    First, I would you not use any rounding at all until the very end of your calculations.

    What do you mean by weighted average? There is no way the difference between Access and Excel should be as large as you show in the example, so the calculation methods must not be the same. However - how did you get the values into MS Access - did you import them from Excel? If you did import them, the values you get on import are as they are formatted in Excel -meaning that if you only display 1 decimal point in Excel, that's the accuracy you get in Access.

    If you have any VBA code, look for variables you might have accidently defined as Integer type - that will mess up calculations in a hurry, and there is no warning.

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

Similar Threads

  1. Creating a custom toolbar floating for reports
    By gangel in forum Programming
    Replies: 3
    Last Post: 11-30-2015, 10:42 AM
  2. Replies: 1
    Last Post: 11-22-2015, 04:36 PM
  3. Replies: 2
    Last Post: 05-10-2013, 03:37 PM
  4. Replies: 10
    Last Post: 07-25-2011, 12:07 PM
  5. Floating Text Box
    By glasgowlad1999 in forum Forms
    Replies: 1
    Last Post: 02-02-2011, 12:10 PM

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