sum???

Hi all.

How can I make sum of one column in table called "Probno" in Access database? Column name is "Ukupno".

So far I've got this code, but it doesn't work. Column "Ukupno" is formated as number.

Dim MyConn As ADODB.Connection
Dim MyRecSet As ADODB.Recordset
Set MyConn = New ADODB.Connection

MyConn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source= " & App.Path & "\Podaci\Baza.mdb;"
MyConn.Open

sqlStr48 = "SELECT Sum(Ukupno) AS SumOfUkupno FROM Probno"

MyConn.Execute (sqlStr48)

MsgBox SumOfUkupno

MyConn.Close


resoult of this code is BLANKO, NOTHING, NADA, NIENTE (don't know in what language to write resoult!?!?!?!"


Sorry but i'm desperate
Please help me?

Hi all.

How can I make sum of one column in table called "Probno" in Access database? Column name is "Ukupno".

So far I've got this code, but it doesn't work. Column "Ukupno" is formated as number.

Dim MyConn As ADODB.Connection
Dim MyRecSet As ADODB.Recordset
Set MyConn = New ADODB.Connection

MyConn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source= " & App.Path & "\Podaci\Baza.mdb;"
MyConn.Open

sqlStr48 = "SELECT Sum(Ukupno) AS SumOfUkupno FROM Probno"

MyConn.Execute (sqlStr48)

MsgBox SumOfUkupno

MyConn.Close


resoult of this code is BLANKO, NOTHING, NADA, NIENTE (don't know in what language to write resoult!?!?!?!"


Sorry but i'm desperate
Please help me?

Your problem is in this line: MyConn.Execute (sqlStr48)

You can't execute a SELECT query. You have to open a recordset on it instead: MyRecSet.Open sqlStr48, MyConn, adOpenStatic
Then: MsgBox MyRecSet!SumOfUkupno

Thank you msmeth for reply

Now I get error on line you give me:

MyRecSet.Open sqlStr1, MyConn, adOpenStatic


and error is variable not set

Thank you msmeth for reply

Now I get error on line you give me:

MyRecSet.Open sqlStr1, MyConn, adOpenStatic


and error is variable not set

Oh. I thought you declared MyRecSet as New ADODB.Recordset, but you just have it as ADODB.Recordset. Before you open the recordset you need to set it with Set MyRecSet = New ADODB.Recordset

Since you are passing in a string command as the recordset, you should add a default lock type and command parameter in your rs.Open line like so:



MyRecSet.Open sqlStr48, MyConn, adOpenStatic, adLockOptimistic, adCmdText