Microsoft Access modFunction

round

Microsoft Access' Round() function is different from the one implemented in Microsoft Excel. The behavious can be imitated using a user defined function.

The "round2 Solution" is limited by the size of the long data type (roughly 2 billion):

Public Function round2(ByVal dblValue As Double, intDecimal As Integer) As Double
    Dim dblResult As Double
    
    Dim lngFactor As Long
    
    lngFactor = 10 ^ (intDecimal)
    dblResult = CLng(dblValue * lngFactor) / lngFactor
    
    round2 = dblResult
End Function

The "round3 Solution" is limited by the amount of decimal places of the currency data type (4 decimal places):

Public Function round3(ByVal dblValue As Double, intDecimal As Integer) As Double
    Dim dblResult As Double
    
    Dim lngFactor As Long
    
    lngFactor = 10 ^ (intDecimal)
    dblResult = CCur(dblValue / lngFactor) * lngFactor
    
    round3 = dblResult
End Function

lookupRecordset

The domain aggregate functions suffer from performance problems. This function performs a simple lookup in a recordset. The performance of this function compared to the built-in function still has to be proven.

Public Function lookupRecordset( _
    ByVal strFieldName As String, _
    ByVal strSource As String, _
    Optional ByVal strCriteria As String = vbNullString _
    ) As Variant
    Dim varResult as Variant
    
    Dim strSql As String
    Dim rst As Recordset
    
    strSql = _
        "SELECT " & strFieldName & "    " & vbCrLf & _
        "  FROM " & strSource & "       "
    If strCriteria > vbNullString Then
        strSql = strSql & vbCrLf & _
            " WHERE " & strCriteria
    End If
    Set rst = CurrentDb.OpenRecordset(strSql, dbOpenSnapshot)
    With rst
        If .EOF Then
                varResult = Null
            Else
                varResult = .Fields(0)
        End If
        .Close
    End With
    
    lookupRecordset = varResult
End Function