Alternative Solution to DCount
Public Function ESQLCount(strField As String, strTable As String, Optional Criteria As Variant) As Variant Dim rs As ADODB.Recordset 'To retrieve the value to find. Dim varResult As Variant 'Return value for function. Dim strSQL As String 'SQL statement. Dim lngLen As Long 'Length of string. 'Initialize to null. varResult = Null 'Encapsulate Domain in brackets if none exist to allow special characters in the Domain string If Left$(strTable, 1) <> "[" Then strTable = "[" & strTable & "]" End If 'Build the SQL string. strSQL = "SELECT COUNT(" & strField & ") AS TotalCount FROM " & strTable If Not IsMissing(Criteria) Then strSQL = strSQL & " WHERE " & Criteria End If strSQL = strSQL & ";" 'Lookup the value. OpenMyRecordset rs, strSQL, rrOpenForwardOnly, rrLockReadOnly, True varResult = Nz(rs.Fields("TotalCount"), 0) rs.Close 'Assign the return value. ESQLCount = varResult ErrEx.CatchAll MsgBox "Error " & err.Number & ": " & err.Description, vbCritical, "Unexpected error" Resume Next ErrEx.Finally Set rs = Nothing End Function
Source: accessexperts.com