How to Count the result of a query in VBA?

  • Thread starter Thread starter Guest
  • Start date Start date
G

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
 
You have two ways:

Use dcount()

The syntax is:

? dcount("*","table name", "criteria")

note that "criteria" is a standard sql where clause. So, take the "where"
clause from your strSQL..and use that..


eg:

dim lngInMyList as long

lngInMyList = dcount("*","tblCustmers","City = 'Edmonton' ")

msgbox "number of customers in edmonton is = " & lngInMyList

You can also use the full sql string, but really, not a lot of a point to do
that.

dim rstRecords as dao.RecordSet

set rstReocrds = currentdb.OpenReocrdSet("select * from tblCustomers where
city = 'Edmonton' ")
rstReocrds.MoveLast

lngInMyList = rstreocrds.ReocrdCount
rstRecords.Close
 
Back
Top