435,561 Members | 3,010 Online
Need help? Post your question and get tips & solutions from a community of 435,561 IT Pros & Developers. It's quick & easy.

 P: n/a Hello All, I am using VBA in access to perform some calculations. There is a particular sumation that is wrong (barely). this code is withing a loop. TDist = TDist + TempDist Both TDist and TempDist are declared as doubles: Dim TDist As Double Dim TempDist As Double After looping through 7000+ iterations the following addition produces this result These are the exact values of the variables: TDist = 6388254.993 TempDist = 36.334 When: TDist = TDist + TempDist TDist = 6388291.32699999 TDist is suppose to be 6388291.327 Although the difference is negligable and a simple Format would fix it. I noticed it only happened here, I can't reproduce it in the Immediate window. I am worried if it's going to generate a much larger error. And when its accumulative, it tends to grow errors like this, especially when running 200k iterations or more. Is this something anybody has experienced? Is there a solution to prevent this error? Or is this just some highly unlikely situation that occured to me for a very remote and Highly Unlikely combination of data? (Sounds alot like Douglas Adams) - GL Jun 27 '08 #1
10 Replies

 P: n/a Guillermo_Lopez wrote: Hello All, I am using VBA in access to perform some calculations. There is a particular sumation that is wrong (barely). this code is withing a loop. TDist = TDist + TempDist Both TDist and TempDist are declared as doubles: Dim TDist As Double Dim TempDist As Double After looping through 7000+ iterations the following addition produces this result These are the exact values of the variables: TDist = 6388254.993 TempDist = 36.334 When: TDist = TDist + TempDist TDist = 6388291.32699999 TDist is suppose to be 6388291.327 Although the difference is negligable and a simple Format would fix it. I noticed it only happened here, I can't reproduce it in the Immediate window. I am worried if it's going to generate a much larger error. And when its accumulative, it tends to grow errors like this, especially when running 200k iterations or more. Is this something anybody has experienced? Is there a solution to prevent this error? Or is this just some highly unlikely situation that occured to me for a very remote and Highly Unlikely combination of data? (Sounds alot like Douglas Adams) - GL Floating point numbers in computers are imprecise because they are stored as binary. If you Google on the topic you should find tons of sites describing the issue. You have to code around that or use inexact comparisons rather than testing for equality. -- Rick Brandt, Microsoft Access MVP Email (as appropriate) to... RBrandt at Hunter dot com Jun 27 '08 #2

 P: n/a "Guillermo_Lopez" Try using the Round function: Debug.Print Round(TDist + TempDist, 3) Result: 6388291.327 Jun 27 '08 #3

 P: n/a On Apr 24, 8:22*pm, "Stuart McCall" Try using the Round function: Debug.Print Round(TDist + TempDist, 3) Result: 6388291.327- Hide quoted text - - Show quoted text - Curious. I guess i'll have to use Round(TDIst + TempDist,7). I got into the articles about Floating Point Numbers and their limitations. Thanks for your help. - GL Jun 27 '08 #4

 P: n/a On Apr 25, 4:44*pm, Guillermo_Lopez Try using the Round function: Debug.Print Round(TDist + TempDist, 3) Result: 6388291.327- Hide quoted text - - Show quoted text - Curious. I guess i'll have to use Round(TDIst + TempDist,7). * I got into the articles about Floating Point Numbers and their limitations. Thanks for your help. - GL For many or most non-integer uses currency will solve the problems you describe. From the help file. "Currency variables are stored as 64-bit (8-byte) numbers in an integer format, scaled by 10,000 to give a fixed-point number with 15 digits to the left of the decimal point and 4 digits to the right. This representation provides a range of -922,337,203,685,477.5808 to 922,337,203,685,477.5807. The type-declaration character for Currency is the at sign (@). The Currency data type is useful for calculations involving money and for fixed-point calculations in which accuracy is particularly important." Many beginners neglect the second role for currency viz fixed-point calculations in which accuracy is particularly important, and think, "Oh this isn't money so I shouldn't use currency". Nine times our of ten they should use currency. Of course the choice of the term, "Currency" is the culprit here. So MS changed the name for MS-SQL Server to Money and SmallMoney (I believe the second was in anticipation of the plunging American dollar). Now it's more confusing than ever, but that's Microsoft, eh? Probably in 90% (yes, same ratio as above) of the cases when floats are used, they shouldn't be used. So, I recommend using currency unless ten thousandths are important to your application. Jun 27 '08 #5

 P: n/a "lyle fairfield"

 P: n/a On Apr 25, 9:48*pm, "Larry Linson"

 P: n/a On Apr 25, 6:05*pm, lyle fairfield

 P: n/a On Apr 29, 1:17*pm, CDMAPos...@fortunejames.com wrote: Both Double and Currency use 8 bytes so as far as discretization goes they have the same potential accuracy. Do they use the bits of the bytes in the same way? >*The Decimal data type has 14 bytes and therefore has more native potential for finer resolution with appropriate manipulation. Where do those 14 bytes live? Are they part of a 16 byte variant? Have you explored how these work? *The value of the Currency data type over Double, in my experience, only shows up in two situations. *The first is to store Currency values in a way that makes it obvious that the field is about money. *The second is in a simple summation. *The Currency data type has no advantage over Double for almost any kind of calculation beyond that, even multiplication, let alone exponentiation. Hmmmm. I must be doing something wrong here: Sub temp() Dim Message\$ Dim Multiplicand As Double Dim Multiplier As Double Dim PredictedProduct As Double Dim Product As Double Multiplicand = CDbl(1 / 10) Multiplier = CDbl(1 / 10) PredictedProduct = CDbl(1 / 100) Product = Multiplier * Multiplicand If PredictedProduct = Product Then Message = " equals " Else Message = " doesn't equal " End If Debug.Print "Double: " & Product & Message & PredictedProduct 'Double: 0.01 doesn't equal 0.01 End Sub Sub temp2() Dim Message\$ Dim Multiplicand As Currency Dim Multiplier As Currency Dim PredictedProduct As Currency Dim Product As Currency Multiplicand = CDbl(1 / 10) Multiplier = CDbl(1 / 10) PredictedProduct = CDbl(1 / 100) Product = Multiplier * Multiplicand If PredictedProduct = Product Then Message = " equals " Else Message = " doesn't equal " End If Debug.Print "Currency: " & Product & Message & PredictedProduct 'Currency: 0.01 equals 0.01 End Sub What is it? Jun 27 '08 #9

 P: n/a On Apr 29, 2:08*pm, lyle fairfield