variable

  • Thread starter Thread starter Dan
  • Start date Start date
D

Dan

I have an SQL statement that just returns the count and I
was wondering how to have a variable in VBA equal to it.
 
(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
 
Back
Top