sum function not working: need HELP

[santosh1]

Hi

I have a table called Royalty with sumRoyalty field. I am trying to calculate the sum of all the royalties.

I am using the sum function to calculate the sum of all the royalties but it doesn't work. It just gets the first royalty number. Can you suggest what I am doing wrong and how I can fix it?
Thankyou.

dim cumRoyalty2, strSQL as string
dim rsCumSales as recordset

strSQL = "Select Sum(Royalty.sumRoyalty) as SumofROYALTY from Royalty;"
Set rsCumSales = db.OpenRecordset(strSQL, dbOpenDynaset)
If (Not rsCumSales.EOF) Then
If Not IsNull(rsCumSales!SumofROYALTY) Then
cumRoyalty2 = rsCumSales!SumofROYALTY
Else
cumRoyalty2 = 0
End If
Else
cumRoyalty2 = 0
End If

 

[cmmrfrds]

Do you have an identifier for each royalty like royalty id, then you need to group by the id.

strSQL = "Select RoyaltyID, Sum(Royalty.sumRoyalty) as SumofROYALTY from Royalty Group By RoyaltyID;"

 

[KevinMoore]

Your query looks good to me. I'm not sure what you mean when you say the first number. How about trying:

cumRoyalty2 = DSum("sumRoyalty","Royalty")

Kevin

 

[santosh1]

Thanks to you all. Your suggestions worked.


cheerz