asked on

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?

ASKER CERTIFIED SOLUTION

Guy Hengel [angelIII / a3]

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

junglerover77

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.

JackOfPH

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.

dwe0608

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 ...

Ark

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