Round to 2 decimals

[dodgyone]

I have been trying to my figures to round correctly within Access.

Example:

(296/365) * 3.4 = 2.75726
Round to 2 decimals it should = 2.76
Round((296/365) * 3.4,2) sets it to = 2.75

Does anybody have any suggestions?

 

[HarleyQuinn]

What version of Access are you using??

The code you posted for me (Access 2k3) correctly returns 2.76

Cheers

HarleyQuinn
---------------------------------
Get the most out of Tek-Tips, read FAQ222-2244 before posting.

 

[hwkranger]

X*X*X*X*X**X

see faq705-3734.

X*X*X*X**X*X

I wrote a rounding function due to issues long ago, but It's ugly. Ill post it, but use at your own peril, if you read the comments, it's about as painful as watching a monkey write code. lol

Public Function myRound(ByVal NumberBeingRounded As Double, Optional NumOfPlaces As Integer = 0, Optional RoundUpOrDown As Integer = 0) As Double
'RoundUpOrDown -  1 Always Rounds UP, 2 Always Rounds DOWN, NULL allows for rounding to nearest number

'Randall Vollen
'National City Bank
'This was created, because it was brought to my attention by someone that
'there is supposedly 'no way' to round in access.  I looked into it further and found that
'there is no 'easy' way to do it.  This is a very simplistic piece of code that takes in
'any number with any number of decimal places and rounds to the decimal of your choice.

'Mind that you may be led to believie the following will round for you:
'-1 * fix(number * -100)/100 or
'-1 * cint(number * -100)/100

'Unfortunately due to the nature of the truncation of Access's VBA if you use an EVEN Number that happens to fall on
'a half (82.5) then it will always round down- otherwise that is a great one line train of thought.

'***    stuff to delete     ***
'TEST DATA that was thrown in:
'Dim NumberBeingRounded As Double
'NumberBeingRounded = 83.55769921
'Dim NumOfPlaces As Integer
'NumOfPlaces = 7
'*** end of stuff to delete ***

Dim Y As Long 'this holds whole part of the number or the part that will eventually be rounded
Dim z As Double 'this is the part that decideds if the number is rounded up or down
Dim Factor As Double 'this holds number 1 - 1,000,000 that decides to what place to round
Dim HalfPoint As Double

'if you don't specify the number of places, then it's rounded to the nearest whole number
'If IsNull(NumOfPlaces) Then
'    NumOfPlaces = 0
'End If

'sets which decimal to round to
Factor = 10 ^ NumOfPlaces

If IsNull(RoundUpOrDown) Or RoundUpOrDown = 0 Then
'No Forcing at all
    HalfPoint = 0.5
ElseIf RoundUpOrDown = UP Then
'Force rounding up
    HalfPoint = 0
    If NumberBeingRounded * Factor = Int(NumberBeingRounded * Factor) Then
        'means they are trying to round a whole number
        HalfPoint = 1
    End If
Else
    'Force rounding down
    HalfPoint = 1
End If

'multiply number by factor, so we can round
NumberBeingRounded = NumberBeingRounded * Factor

'rounding
    Y = Fix(NumberBeingRounded)
    z = NumberBeingRounded - Y
    NumberBeingRounded = Fix(NumberBeingRounded)
    If z >= HalfPoint And z <> 0 Then
        NumberBeingRounded = NumberBeingRounded + 1
    End If
'end of rounding

'return number back to it's original
NumberBeingRounded = NumberBeingRounded / Abs(Factor)

'return value
myRound = NumberBeingRounded
End Function

Randall Vollen
Merrill Lynch

 

[PaulBricker]

This FAQ may help some as well.

faq701-3133


Paul

 

[barny2006]

there are also other considerations:
if the digit is odd, it rounds up on 5
if the digit is even, it rounds down. e.g.
123.435 rounds up to 123.44
123.465 rounds down to 123.46

 

[PaulBricker]

Just something to muddy the waters here a little. I retested my FAQ in VB 6.3 and it does not work the same as in VB 6.0. I'm not sure why, but the INT() function works differently. For example to round to two places
Int((123.455 +.005)*100)/100 the mathmatical process should be

123.455 + .005 = 123.46
123.46 * 100 = 12346
Int(12346) = 12346
12346/100 = 123.46

VB 6.3 returns 123.45
VB 6.0 returns 123.46

So the version of VB that you are running will make a lot of difference as well.

Paul