Rounding in Access

Question

The following code is working fine, however it is not rounding the stored values to the nearest pence, for example 8.025 is coming up instead of 8.01 can anyone suggest a fix?

Public Function Fs_Update_AccInvoices_Nexum() As Boolean
    Dim adoRsInvoiceDCID As New ADODB.Recordset
    Dim adoRsNexumInvoices As New ADODB.Recordset

    On Error Resume Next
    adoRsInvoiceDCID.Open "SELECT * FROM [tInvoiceDCID] where Issued=0" _
        , CurrentProject.Connection, 2, 2
    While Not adoRsInvoiceDCID.EOF
        adoRsNexumInvoices.Open "SELECT * FROM [tPrintInvoiceNumbersNexum] " _
            & " WHERE InvoiceID=" & adoRsInvoiceDCID("InvoiceID") _
            , CurrentProject.Connection, 2, 2
        If Not adoRsNexumInvoices.EOF Then
            DoCmd.SetWarnings off
            DoCmd.RunSQL "Update [Acc Invoices t Nexum] " _
                & " SET [Total Due] = Round((Fees/0.8)+(VAT/0.8)+OutLays,2)" _
                & " Fees = Round(Fees/0.8,2), VAT = Round(Vat/0.8,2)" _
                & " WHERE Invoice=" & adoRsNexumInvoices("PrintingasINVOICE")
        End If
        adoRsNexumInvoices.Close

        adoRsInvoiceDCID.MoveNext
    Wend
    adoRsInvoiceDCID.Close
End Function

Cheers Ross

score 2 · Answer 1 · answered Oct 12 '12 at 19:17

Quick note: I've noticed some inaccuracies in vba's rounding function which the format function doesn't fix. In my particular case, I was trying to round the number 3687.23486

round(3687.23486) = 3687.23

format(3687.23486, "#.00") = 3687.23

under the traditional round to nearest rules, this should result in 3687.24 I've seen several custom functions posted to various forums to address rounding problems, but none worked for me, so I wrote my own.

    Function trueRound(ByVal varNumber As Variant, ByVal intDecimals As Integer) As Double
    If IsNull(varNumber) Then
        trueRound = 0
        Exit Function
    End If
    Dim decimals As Integer, testNumber As Double
    decimals = 0
    If InStr(varNumber, ".") > 0 Then decimals = Int(Len(varNumber)) - Int(Len(Fix(varNumber)) + 1)
    If decimals = 0 Or intDecimals > decimals Then
        trueRound = varNumber
        Exit Function
    End If
    Do Until Len(varNumber) - Len(Fix(varNumber)) - 1 <= intDecimals
        testNumber = varNumber * 10 ^ (decimals - 1)
        varNumber = Round(testNumber, 0) / 10 ^ (decimals - 1)
        decimals = decimals - 1
    Loop
    trueRound = varNumber
End Function

I hashed it out pretty quick, so there's no error handling, and a null value passed to the function results in 0, which may not be ideal for all situations. I use this regularly in some pretty large queries, hope it can help someone else.

There are lots of different sets of rules for rounding.. I was specifically looking for a way to round half away from zero with a bias toward larger numbers. I know "trueround" is a misnomer, since this is not the most common method of rounding.. If nothing else this may help someone to tailor their own rounding functions to fit whatever rules they need. — Robin Cofer, Oct 12 '12 at 21:08

score 1 · Answer 2 · edited May 23 '17 at 11:55

1

"The Round function performs round to even, which is different from round to larger." --Microsoft

Debug.Print Round(19.955, 2)
'Answer: 19.95

Debug.Print Format(19.955, "#.00")
'Answer: 19.96

Rounding in Access

2 Answers2

Linked