Sum / Filter on ADO Recordset

I have an ADO recordset that I later apply a Filter to. Now, can't I also do a Sum on individual columns in that filtered recordset? How?
Who is Participating?
Guy Hengel [angelIII / a3]Connect With a Mentor Billing EngineerCommented:
you have to do that with code.

now, I had worked in my early projects with filtered recordsets, and meanwhile found out that running the (amended) query on the database will be at least as fast as doing filtering etc... the bigger the main recordset, the faster the results by running the query instead of filtering the recordset
With ADO 2.7 or higher versions, you can apply a filter to a Recordset. Here is the sample code:

After a filter is applied to a recordset, you can just loop through the filtered recordset and do a sum on any columns as you want.

do a looping. for example.

dim sum

rs.filter = "fldname = `Joseph`"

do until rs.eof
    sum = sum + rs!age

the example will add all the age whose name is Joseph.

you can also use an SQL passthrough query (using ADO), which is in essence querying the same table twice with different parameters - if you post the sql query you are using I'll see if I can knock it out for you ...
You can use SQL statement for both sum and filter:

'Assume you have a table "OrdersTable" with fields
'"Prices", "CustomerID" and "CustomerName"

Dim strSQL As StringDim rst As ADODB.Recordset

strSQL = "SELECT SUM(Prices) As mySum, COUNT(CustomerID) As myCount FROM OrdersTable WHERE CustomerName =`Joseph`"
'mCN is connection name
Set rst = mCN.Execute(strSQL, , adCmdText)
Debug.Print "Sum (for Joseph)  = " & rst("mySum")
Debug.Print "Count (for Joseph) = " & rst("myCount")
Set rst = Nothing
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.