How do I count the # of records in a query?

[Guest]

Hello everyone

My task is to query a table in a VBA function and then return the count to
the calling environment.

How do I do that inside the function?

The profile of the function is something like this:

function CountItems ( strSQL as String) as integer

Many thanks

 

[Guest]

I'm not sure if that what you are looking fr, but if you want to pass the
function SQL and you want the function to return the number of records
returned from that SQL, try

Function CountItems(strSQL As String) As Integer
Dim MyDb As DAO.Database, MyRec As DAO.Recordset
Set MyDb = CurrentDb
Set MyRec = MyDb.OpenRecordset(strSQL)
If Not MyRec.EOF Then
MyRec.MoveLast
CountItems = MyRec.RecordCount
Else
CountItems = 0
End If
End Function