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?
LVL 9
GivenRandyAsked:
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
0
 
junglerover77Commented:
With ADO 2.7 or higher versions, you can apply a filter to a Recordset. Here is the sample code:

http://msdn.microsoft.com/library/en-us/ado270/htm/mdprofilterx.asp

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.

0
 
JackOfPHCommented:
do a looping. for example.

dim sum

rs.filter = "fldname = `Joseph`"

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


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

0
 
dwe0608Commented:
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 ...
0
 
ArkCommented:
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")
rst.Close
Set rst = Nothing
0
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.