SQL

[turfbult]

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!!??

[Skeen]

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

[Anakim]

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)

[turfbult]

Anakim,

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

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

[kovan]

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"

[turfbult]

Thanks kovan,

This works 100%

[kovan]