hey i have a datagrid on a form which is linked to a query from acess. there is a filter on the form and i would like to be able to add up ALL the values in a list. eg price is filtered so there is
99
77
45

as one of the columns, how can i total these figures up.
ive tried adodc1.recordset!price but they only returns "99"

[Edited by honey0 on 18-03-2006 at 09:17 PM GMT]
anyway of getting the total from this filterd access query would really help.

[Edited by honey0 on 18-03-2006 at 09:55 PM GMT]

[Edited by honey0 on 18-03-2006 at 09:59 PM GMT]

hello..

where do you want to display the total?? if you wist to sum up the data in a single column all you have to do is to use the aggregate function sum() which would look like this when used in a query... in case you're familiar..

rs.open "select sum(field_to_sum) as Total from table1",....etc.

text1.text = rs!total 'this command will display the total into the text box.

hope this helps...



    

andrew

hey thanks for the help. think i need a bit help with the notation. the field i want to sum is called price and its in query called subfrom, currently open on the form thouigh a adodc control.

changed the adodc connection to a adodb, but how do i connect the datagrid to a adodb connection

Dim db As New ADODB.Connection
Dim rs As New ADODB.Recordset
Dim sPath As String ' here we put the path of your database
Dim sSQL As String 'your SQL Statement
Dim total As String

sPath = "C:\Documents and Settings\Administrator\My Documents\jewellery.mdb;"

'initialize the connection string

sConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & sPath

sSQL = "select sum(price) as Total from subform1"

db.ConnectionString = sConn
db.Open

With rs
.ActiveConnection = db
.LockType = adLockOptimistic
.CursorType = adOpenKeyset
.Filter = "[Customer_ID]=" & CLng(CID) <---- tried adding this to filter but not working
.Open sSQL

'Do While Not .EOF
'MsgBox (.Fields("Price").Value)
'.MoveNext
'Loop

End With
Text11.Text = rs!total 'this command will display the total into the text box.
rs.Close

Set rs = Nothing
db.Close
Set db = Nothing

Using this works and returns the Sum of all the fields, is there anway to filter this as i have a filter on the adodc that the datagrid is connected to

also how can i usethis adodb connection for the datagrid, i tried datagrid1.recordsouce = rs


[Edited by honey0 on 19-03-2006 at 01:23 PM GMT]

YOu need to use SET keyword when setting datasource property

Set dg.datasource=rs

Filter statement should be placed after Open statement. If you dont need all the records from table, just the filtered one, then there is no need to fetch all the data from table, just the filtered one:

sSQL = "select sum(price) as Total from subform1 WHERE [Customer_ID]=" & CLng(CID)

____________________________
If you find the answer helpful, please mark this topic as solved.