# 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.

You may also want to read:

VBA Excel Access roundup rounddown Function

Excel Access MRound custom Function round to nearest multiple

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

Manager@IBM
Wyman is human resources professional specialized in implementation of HR information system.

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