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 Round 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

Some custom Round Function you can find on the Internet do not consider cases for negative number , but this one does.

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

Wyman W
Wyman is a Human Resources professional based in Hong Kong, specialized in business analysis, project management, data transformation with Access and Excel.

He is also a:
- Microsoft Most Valuable Professional (Excel)
- Microsoft Community Contributor
- Microsoft Office Specialist in Access / Excel
- Microsoft Specialist in MS Project
- Microsoft Technical Associate
- Microsoft Certified Professional
- IBM SPSS Specialist

Leave a Reply

Your email address will not be published. Required fields are marked *