Sum of fields in db

[Dman]

I am pretty new so please be patient.

How would I get the sum of some fields in the db and place the total into a variable? If I run this statement I can see all of the amounts in a messagebox but for some reason I just can't seem to figure out how to make it total and place it into a variable.

       dbCursor = SQL1.ExecQuery("SELECT amount FROM panels WHERE vehid = " & lblVehID.Text)

I have tried the statement below but it gives me errors. One says that column 'amount' does not exist and the other one is an invalid double:"".

       dbCursor = SQL1.ExecQuery("SELECT SUM(amount) as total_price FROM panels WHERE vehid = " & lblVehID.Text)
            vehtotal = total_price

 

[NJDude]

You could do something like this:

dbCursor = SQL1.ExecQuery("SELECT amount FROM panels WHERE vehid = " & lblVehID.Text)

For I = 0 To dbCursor.RowCount -1 

    dbCursor.Position = I
    myAmount = dbCursor.GetString("amount")
    Total = Total + myAmount

Next

dbCursor.Close

MsgBox("Total = " & Total, "")

I haven't tested this but it should work.

 

[Dman]

Thanks, I have tried that also but it gives me an invalid double:"" error. I just tried it again and the same thing.

How can something so easy be so hard.

 

[klaus]

You should use this:

vehtotal = SQL1.ExecQuerySingleResult("SELECT sum(amount) FROM panels WHERE vehid = " & lblVehID.Text)

Best regards.

 

[Dman]

That worked perfectly. Thanks guys!