Re: best practices.rounding

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

You really just have to use currency.

You can choose to force the result to 2 decimal places (round to 2), but
REGARDLESS, YOU MUST use
currency data type. YOU CAN NOT USE double.

eg:

Public Sub TestAdd()
Dim MyNumber As Single
Dim i As Integer

For i = 1 To 10
MyNumber = MyNumber + 1.01
Debug.Print MyNumber
Next i
End Sub

Here is the actual outpput of the above:
1.01
2.02
3.03
4.04
5.05
6.06
7.070001
8.080001
9.090001
10.1


You can see that after just 7 addtions..already rounding is occuring

and if we add the follwing line of code to the end of the above:

if MyNumber = 10.1 = True then
msgbox "the number is 10.1"
else
msgbox "the number is somthing else"
endif

The above will actuall produce:

the number is something else

So, regardless of what links, what concepts. If you are writing business
applications in Excel, or ms-access, you MUST USE currency data type. Using
double vars can still result in rounding errors. I would not trust that
rounding code you have.

So, has this issue been done to death? Hum, not really, but the simple
answer is that you must use currency data types. If you don't want values
beyond 2 decimal places, then build a function that truncates to 2 decimal
places, and always store that value. But, the rule still remains that you
have to use currency data type, and can NOT use double type if you don't
want any rounding errors.

--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
pleaseNOOSpamKallal@xxxxxxx
http://www.members.shaw.ca/AlbertKallal


.

Relevant Pages

  • Re: best practices.rounding
    ... What round function would you suggest, to always round to 1 or 2 decimal, NOT ... Dim MyNumber As Single ... You can see that after just 7 addtions..already rounding is occuring ... applications in Excel, or ms-access, you MUST USE currency data type. ...
    (microsoft.public.access.gettingstarted)
  • Re: How do I keep number field from automatically rounding up fraction
    ... In addition to FredG's suggestion, if you will only ever need 4 decimal ... consider using the Currency data type. ... issues with rounding errors. ... How do I format that to stop rounding up to the next whole hour? ...
    (microsoft.public.access.tablesdbdesign)