Microsoft Access modFunction
Jump to navigation
Jump to search
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