Help with Rounding in Visual Basic

[UTLee]

I have the following query that pulls certain account information out of our Access database and puts it into an excel template:

Function arrAccounts()    Dim arrAccts()
    Dim i As Integer
    Dim rsAccounts As New ADODB.Recordset
    Dim strSql As String
    Dim intNumAccounts As Integer
    strSql = "SELECT tblClients.client, tblClients.qtrlyFeePercent As qtrlyFeePercent, tblAccountHistory.AccountID As AccountId, tblAccounts.account as AccountName, tblAccounts.billToAccount As BillToAccount, tblAccountHistory.EndingBal As EndingBal, tblAccountHistory.TransDate "
    strSql = strSql & "FROM (tblClients INNER JOIN tblAccountHistory ON tblClients.client=tblAccountHistory.ClientID) INNER JOIN tblAccounts ON (tblClients.client=tblAccounts.client) AND (tblAccountHistory.AccountID=tblAccounts.accountID) "
    strSql = strSql & "WHERE (((tblAccountHistory.TransDate) = #" & Me.ReportDate & "#) AND (tblClients.client = '" & Me.Client & "')) ORDER BY tblAccounts.account;"
    
    rsAccounts.Open strSql, cn, adOpenForwardOnly, adLockReadOnly
    If rsAccounts.BOF And rsAccounts.EOF Then
        
        ReDim arrAccts(1, 6)
        arrAccts(0, 0) = "_"
        arrAccts(0, 1) = "_"
        arrAccts(0, 2) = 0
        arrAccts(0, 3) = 0
        arrAccts(0, 4) = 0
        arrAccts(0, 5) = "_"
        arrAccounts = arrAccts
        Exit Function
    End If
    
    Do While Not rsAccounts.EOF
        intNumAccounts = intNumAccounts + 1
        rsAccounts.MoveNext
    Loop
    rsAccounts.MoveFirst
    Dim j As Integer
    ReDim arrAccts(intNumAccounts - 1, 6)
    Do While Not rsAccounts.EOF
        arrAccts(j, 0) = rsAccounts("AccountName")
        arrAccts(j, 1) = rsAccounts("AccountId")
        arrAccts(j, 2) = rsAccounts("EndingBal")
        arrAccts(j, 3) = rsAccounts("qtrlyFeePercent")
        arrAccts(j, 4) = rsAccounts("EndingBal") * rsAccounts("qtrlyFeePercent")
        arrAccts(j, 5) = rsAccounts("BillToAccount")
        
        j = j + 1
        rsAccounts.MoveNext
    Loop
    arrAccounts = arrAccts
        
End Function

When the code calculates:
arrAccts(j, 4) = rsAccounts("EndingBal") * rsAccounts("qtrlyFeePercent")


there can sometimes be many decimal places and since these are dollar value calculations, I'd like to round the calculation to 2 decimal places. I understand I can change this part of the code to:
arrAccts(j, 4) = Round(rsAccounts("EndingBal") * rsAccounts("qtrlyFeePercent"),2)
but this doesn't seem to work properly in certain instances. For example, if endingbal = 9,754 and qtrylfeepercent = .0025, the unrounded answer is 24.385 and when I round it to two decimal places it should display 24.39 but instead its showing 24.38...

Any ideas what is going on here??

[pbaldy]

The reason why (bankers rounding, in the middle of the page) and some alternatives:

http://allenbrowne.com/round.html

[June7]

Round() function in Access and VBA does not follow same rules as Round() function in Excel used in cells (VBA in Excel uses bankers). Thank you M$.

To avoid bankers rounding I built a custom function for rounding.

Function RRound(FieldValue As Variant, Optional intPos As Integer = 0) As Variant
'--------------------------------------------------
' Function RRound() rounds value to designated decimal position.
' If argument does not contain data, RRound returns null.
' Use because intrinsic Round uses even/odd (banker's) rounding.
' Also, Format and FormatNumber functions don't use even/odd but
' generate a string result which is often inconvenient.
'--------------------------------------------------
Dim strZeros As String
Dim i As Integer
If intPos = 0 Then
    strZeros = 0
Else
    For i = 1 To intPos
        strZeros = strZeros & 0
    Next
    strZeros = "0." & strZeros
End If
RRound = IIf(Not IsNull(FieldValue), Val(Format(FieldValue, strZeros)), Null)
End Function

[UTLee]

Thank you both for your quick replies! I will try these first thing tomorrow am...

[pbaldy]

No problem.