(1)
Use DLookup function in VBA. Store the SQL as a query (name it qryCount, for
example) and then use this expression:
MyCountVariable = DLookup("CountFieldName", "qryCount")
(2)
Alternatively, you could use a stored Select query that returns all records
that would be counted, and then use DCount function:
MyCountVariable = DCount("*", "SelectQueryName")
(3)
Again alternatively, you could open a recordset in VBA code and count the
records. This could use the SQL statement directly.
Dim rst As DAO.Recordset
Dim dbs As DAO.Database
Dim strSQL As String
Dim MyCountVariable As Long
Set dbs = CurrentDb
strSQL = "Your SQL Statement Text String"
Set rst = dbs.OpenRecordset(strSQL, dbOpenDynaset)
rst.MoveLast
MyCountVariable = rst.RecordCount
rst.Close
Set rst = Nothing
dbs.Close
Set dbs = Nothing
