Is it possible to use RecordCount with Criteria

Question

In this article,
It shows a comparative table how .RecordCount is way faster than any other method use in that article but it did not mention if it can be use with criteria.

That is my question now. Can .recordcount be use with criteria? For example I only want the recordcount to return numbers of record in my invoices table where status is equals to "PAID". If it is possible, How?

Currently I am using for loops on a recordset to do it but seeing that article can possibly increase performance if recordcount can be use with criteria

Thanks in advance

Answer 1

As your table is linked, use any method, but DCount is extremely simple:

PaidInvoiceCount = DCount("*", "InvoiceTable", "[Status] = 'PAID'")

The method in the link you provided is for TableDefs only and is very fast:

Public Function CountRecords()

    Dim lngCount    As Long

    Debug.Print Timer
    lngCount = OpenDatabase("d:\path\test.mdb").OpenRecordset("InvoiceTable").RecordCount
    Debug.Print Timer

End Function

You can modify this to use a recordset:

Public Function CountRecords()

    Dim rs          As DAO.Recordset
    Dim lngCount    As Long

    Debug.Print Timer
    Set rs = OpenDatabase("d:\path\test.mdb").OpenRecordset("Select * From InvoiceTable Where [Status] = 'PAID'", dbOpenSnapshot)
    rs.MoveLast
    lngCount = rsFilter.RecordCount
    Debug.Print Timer

    rs.Close
    Set rs = Nothing

End Function

but then it will run no faster than DCount.