Rounding Issue

[Leesha]

Hi,

I have a query that roounds to a 2 decimals. It is working fine. The problem I'm having is that the number in the 3rd decimal place is a 5 and rather than rounding up its rounding down. I need it to round up. Is this possible?

IE the number is $39,145.785 and if I set it to round its returning $39,145.78. It should be returning $39,145.79

Thanks,
Leesha

[Rudi]

Hi Leesha,

This is a built-in process in Access called "round-to-even logic" See details here.

There does not seem to be a "fixed" solution to your question. Depending on your number and the desired result you seek, thee are a few different solutions to consider. For more details on an alternative expression that would suit your desired result, see examples here and here.

Hans might have a better solution than my two links, so I'd naturally wait for his response too

[HansV]

You could use this custom rounding function:

Code: Select all

Function NRound(v As Variant, n As Long)
    Dim p As Double
    If IsNull(v) Then
        NRound = Null
    ElseIf Not IsNumeric(v) Then
        NRound = CVErr(5)
    Else
        p = 10 ^ n
        NRound = Int(p * v + 0.5) / p
    End If
End Function

(N stands for "nearest")

Instead of using an expression such as Round([Amount], 2), you can use NRound([Amount], 2)

[Leesha]

Hi,
Finally just got back to this. Haven't had a chance to read the links yet. This calculation is done in a query. Is there a way to adapt adapt the code from Hans to a query?
Thanks,
Leesha

[HansV]

You can copy the code that I posted into a standard module (created by selecting Insert > Module in the Visual Basic Editor). You can then use the NRound function in a query, the same way you would use the Round function.