Access Round Function and custom Round Function

This tutorial explains how to use Access Round Function (round to even logic) and create a custom Round Function to simulate Excel Round Function.

Access Round Function – Round to even logic

Access Round Function is completely different from Excel Round Function.

For Excel Found Function, if decimal part is >=0.5, the integer rounds up, otherwise the integer rounds down.

For Access Round Function, as well as Access VBA, Excel VBA, the Round Function uses “Round to even” logic. Integer rounds up if decimal part >=0.6, round down if <=0.4. For case decimal part exactly =0.5, , then round down, otherwise round up(round up to even integer), same logic applies to negative number (See example).

I summarize the Access round to even logic below.

If decimal part >=0.6 Then
   Round up
ElseIf decimal part <=0.4 Then
   Round down
ElseIf decimal part = 0.5 Then
   If integer part is even number Then
        Round down
   ElseIf integer part is odd number Then
        Round up
  End If
End If

Round to even logic has other names:

-unbiased rounding

-convergent rounding

-statistician’s rounding

-Dutch rounding

-Gaussian rounding

-odd-even rounding

-bankers’ rounding

-broken rounding

Syntax of Access Round Function – Round to even logic

Round ( expression, [ decimal_places ] )

If [decimal_places] is omitted, default is 0 (round to integer)

Example of Access Round Function – Round to even logic

Round([Number],0)

Number	Access Round	Excel Round	Check Diff
0.1	0	0
0.2	0	0
0.3	0	0
0.4	0	0
0.5	0	1	integer part is even number, round down
0.6	1	1
0.7	1	1
0.8	1	1
0.9	1	1
1	1	1
1.1	1	1
1.2	1	1
1.3	1	1
1.4	1	1
1.5	2	2	integer part is odd number, round up
1.6	2	2
1.7	2	2
1.8	2	2
1.9	2	2
2	2	2
2.1	2	2
2.2	2	2
2.3	2	2
2.4	2	2
2.5	2	3	integer part is even number, round down
2.6	3	3
2.7	3	3
2.8	3	3
2.9	3	3
3	3	3
3.1	3	3
3.2	3	3
3.3	3	3
3.4	3	3
3.5	4	4	integer part is odd number, round up
3.6	4	4
3.7	4	4
3.8	4	4
3.9	4	4
4	4	4
4.1	4	4
4.2	4	4
4.3	4	4
4.4	4	4
-4.5	4	5	integer part is even number, round down
-4.6	5	5
-4.7	5	5
-4.8	5	5
-4.9	5	5
-5	5	5
-5.1	5	5
-5.2	5	5
-5.3	5	5
-5.4	5	5
-5.5	6	6	integer part is odd number, round up

VBA Code of custom Access Round Function – simulate Excel Round function

Function wRound(pValue As Double, digit) As Double
    Dim ExpandedValue
    Dim IntPart As Long
    Dim FractionPart As Double
    Dim negNumber As Boolean
    If pValue < 0 Then
        pValue = 0 - pValue
        negNumber = True
    End If
    
    ExpandedValue = pValue * (10 ^ digit) 'Retrieve integer part of the number
    IntPart = Int(ExpandedValue)
    FractionPart = ExpandedValue - IntPart 'Retrieve the fraction part of the number
    If FractionPart < 0.5 Then
        result = IntPart / 10 ^ digit
    ElseIf FractionPart >= 0.5 Then
        result = (IntPart + 1) / 10 ^ digit
    End If
    
    If negNumber = True Then
        wRound = 0 - result
    Else
        wRound = result
    End If
End Function

Syntax of custom Access Round Function – simulate Excel Round function

wRound(pValue, digit)

pValue	the decimal value you want to convert
digit	the number of decimal places you want to convert to

Outbound References

http://www.techonthenet.com/access/functions/numeric/round.php

Access Round Function and custom Round Function