SQL

I want to calculate the sum of "price" in a orders table into a variable....

I tried this..

sSql = "select sum(price) into intPrice from orders where client = 007"

This does not work.

Can anyone please help!!??

Hi

I'm quite new to SQL and VB but could you try this:

sSQL= "SELECT Sum(price) FROM Orders WHERE Client = '" & 007 & "' "

Set rsSUM = ConnectionObject.Execute( sSQL )
rsSUM.MoveFirst
SumTotal =rsSUM( "price" )

ssSQL= "INSERT INTO intPrice FieldName Values '" & SumTotal & "'
Set rsInsert = ConnectionObject.Execute( ssSQL )


Its probably bullshit, but I'm trying something similar at the moment and this seems like should work to me, No promisses though.

In abit 'G'

Skeen

Are you wanting to contain this SQL all within the DB environment or are you asigning the result to a VB var?

If the former then the following works for SQL Server...

DECLARE @mySum as DECIMAL (12)

SELECT @mySum =
(SELECT sum(price) FROM orders WHERE blah = blah)

Anakim,

I want to assign the result to a VB variable!!

I'm ripping my hair out on this side!!

Code:

---

dim TotalPrice as Currency
SQL ="SELECT SUM(price)as SumOfPrice FROM Orders WHERE client = 007"
rs.open sql, db
TotalPrice = rs!SumOfPrice

---

that should work
samething as yours, you were just missing "as"

Thanks kovan,

This works 100%

:)