Rounding Error in Access 2013

Question

Can you tell me why Access 2013 would round 410.50 down to 410 instead of rounding up to 411? In this example all of the other values in .5 round up.

Thanks.

Answer 1

In Access Query

Round([Hours],0)

Answer 2

Try an alternative: Int([Hours]+0.5).

Round seems to performs "Banker's rounding".

Answer 3

Thanks. Will that affect the other data where the rounding is correct? I'm curious if that formula you are suggesting will round up on all of all rows rather than the ones that have .5 or greater?

Kind of seems like this is a bug in the Access software? I don't understand why it rounds correctly for others that end in .5? Only seems to be a problem with one of them?

Answer 4

It was my understanding that an amount that ended in .5 or above would be rounded up. Below .5 is rounded down. That has been my experience with other software packages. Could you please tell me how to view detailed examples of banker's rounding and why that was chosen.

I do appreciate your answer. I'm moving on since I don't see a good answer for this. So I'm just curious at this point.

Answer 5

Round doesn't "seem" to perform Banker's Rounding - rounding to nearest even - it does perform Banker's Rounding, as do all the conversion functions that will round: CByte, CInt, CLng, CCur. The only function that performs true 4/5 rounding as you learned in school, is Format.

Further, Round is quite buggy, thus should only be used for coarse rounding, for example plotting a graph.

For any serious or extreme rounding, refer to my rounding methods found at GitHub: VBA.Round

The function, you probably need, is:

' Rounds Value by 4/5 with count of decimals as specified with parameter NumDigitsAfterDecimal.
'
' Rounds to integer if NumDigitsAfterDecimal is zero.
'
' Rounds correctly Value until max/min value limited by a Scaling of 10
' raised to the power of (the number of decimals).
'
' Uses CDec() to prevent bit errors of reals.
'
' Execution time is about 1µs.
'
' 2018-02-09. Gustav Brock, Cactus Data ApS, CPH.
'
Public Function RoundMid( _
    ByVal Value As Variant, _
    Optional ByVal NumDigitsAfterDecimal As Long, _
    Optional ByVal MidwayRoundingToEven As Boolean) _
    As Variant

    Dim Scaling     As Variant
    Dim Half        As Variant
    Dim ScaledValue As Variant
    Dim ReturnValue As Variant

    ' Only round if Value is numeric and ReturnValue can be different from zero.
    If Not IsNumeric(Value) Then
        ' Nothing to do.
        ReturnValue = Null
    ElseIf Value = 0 Then
        ' Nothing to round.
        ' Return Value as is.
        ReturnValue = Value
    Else
        Scaling = CDec(Base10 ^ NumDigitsAfterDecimal)

        If Scaling = 0 Then
            ' A very large value for NumDigitsAfterDecimal has minimized scaling.
            ' Return Value as is.
            ReturnValue = Value
        ElseIf MidwayRoundingToEven Then
            ' Banker's rounding.
            If Scaling = 1 Then
                ReturnValue = Round(Value)
            Else
                ' First try with conversion to Decimal to avoid bit errors for some reals like 32.675.
                ' Very large values for NumDigitsAfterDecimal can cause an out-of-range error when dividing.
                On Error Resume Next
                ScaledValue = Round(CDec(Value) * Scaling)
                ReturnValue = ScaledValue / Scaling
                If Err.Number <> 0 Then
                    ' Decimal overflow.
                    ' Round Value without conversion to Decimal.
                    ReturnValue = Round(Value * Scaling) / Scaling
                End If
            End If
        Else
            ' Standard 4/5 rounding.
            ' Very large values for NumDigitsAfterDecimal can cause an out-of-range error when dividing.
            On Error Resume Next
            Half = CDec(0.5)
            If Value > 0 Then
                ScaledValue = Int(CDec(Value) * Scaling + Half)
            Else
                ScaledValue = -Int(-CDec(Value) * Scaling + Half)
            End If
            ReturnValue = ScaledValue / Scaling
            If Err.Number <> 0 Then
                ' Decimal overflow.
                ' Round Value without conversion to Decimal.
                Half = CDbl(0.5)
                If Value > 0 Then
                    ScaledValue = Int(Value * Scaling + Half)
                Else
                    ScaledValue = -Int(-Value * Scaling + Half)
                End If
                ReturnValue = ScaledValue / Scaling
            End If
        End If
        If Err.Number <> 0 Then
            ' Rounding failed because values are near one of the boundaries of type Double.
            ' Return value as is.
            ReturnValue = Value
        End If
    End If

    RoundMid = ReturnValue

End Function