How to Count the result of a query in VBA?

[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

 

[Albert D. Kallal]

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

 

[Alex Dybenko]

Hi,
you can open recordset, based on strSQL, run rst.movelast and return
rst.recordcount
or run a subquery:

"select count(*) from (" & strSQL & ")"


--
Best regards,
___________
Alex Dybenko (MVP)
http://alexdyb.blogspot.com
http://www.PointLtd.com