sum of field in recordset

  • Thread starter Thread starter Ari via AccessMonster.com
  • Start date Start date
A

Ari via AccessMonster.com

Hallo
Is there please some easy function (maybe DSUM or SUM or whatever) which is
possible to use to get a sum of field in recordset instead of using do-loop ?
Code:

Dim strSQL As String
Dim myData As New ADODB.Recordset
strSQL = "SELECT tblShiftData.Date, tblShiftData.ShiftId, tblDriveData.
StnStart, tblDriveData.StnEnd, tblDriveData.BoreClassId, etc... it is
running fine"
myData.Open strSQL, CurrentProject.Connection, adOpenForwardOnly,
adLockReadOnly
'here I need to insert some easy function to make a sum of for example myData!
StnStart which is Single instead of do-loop
myData.Close

Thanks
Ari
 
Do you need the recordset, or do you only need the sum?

If all you need is the sum, change your SQL to

strSQL = "SELECT Sum(tblDriveData.FieldToSum) FROM etc...

Your recordset will then return a single row, with myData.Fields(0)
containing the sum.

If you need both and don't want to loop, you'll need to use a second
recordset that only returns the sum as above.
 
Unfortunatelly I need the recordset which is quite complicated. Then I need
to make a sum of several fields and also with several different conditions.
Would be nice to have some Sum function but if it does not exist then I must
write the code.
Thanks
Ari
 
If you are wanting to do it outside SQL and in VBA, then look in VBA Help for
the DSum function.
 
I don't believe it's possible to use DSum to calculate the sum of the
records in an open recordset, or do you have something different in mind?
 
Actually, it is. I tested it both on a recordset based on a query and on a
recordset based on a table. The query included two tables. Here is what I
did in the immediate window for the query version:
set rst = currentdb.OpenRecordset("zztest")
?dsum("[mar]",rst.Name)
4272692.156882
 
Really! I did not know that.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Klatuu said:
Actually, it is. I tested it both on a recordset based on a query and on a
recordset based on a table. The query included two tables. Here is what I
did in the immediate window for the query version:
set rst = currentdb.OpenRecordset("zztest")
?dsum("[mar]",rst.Name)
4272692.156882


Douglas J Steele said:
I don't believe it's possible to use DSum to calculate the sum of the
records in an open recordset, or do you have something different in mind?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Help
for I
need then I
must
Click to expand...
Click to expand...
 
I did not test it in a running VBA procedure, only the immediate window. I
don't know that there should be a difference.

Douglas J Steele said:
Really! I did not know that.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Klatuu said:
Actually, it is. I tested it both on a recordset based on a query and on a
recordset based on a table. The query included two tables. Here is what I
did in the immediate window for the query version:
set rst = currentdb.OpenRecordset("zztest")
?dsum("[mar]",rst.Name)
4272692.156882


Douglas J Steele said:
I don't believe it's possible to use DSum to calculate the sum of the
records in an open recordset, or do you have something different in mind?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


If you are wanting to do it outside SQL and in VBA, then look in VBA Help
for
the DSum function.

:

Unfortunatelly I need the recordset which is quite complicated. Then I
need
to make a sum of several fields and also with several different
conditions.
Would be nice to have some Sum function but if it does not exist then I
must
write the code.
Thanks
Ari
Click to expand...
Click to expand...
Click to expand...
 
So I was trying it with no success. Is it really possible in VBA ?
Or perhaps is there any option how to use the already defined string strSQL,
which contains "a good query", to open a query in VBA e.g. qryA and then use
DSum as DSum("[field]", qryA, nejake podminky) ?
I do not want to make a stiff query in Access because there are already
plenty of them in my application.
Thanks
 
Yes, if you view my previous post, you will see the answer.
You just use the DSum on the domain whether it is an existing query or a
table. You cannot use an SQL string. It doesn't matter whether you have the
recordset open or not.

As to using SQL instead of stored queries. Stored queries execute faster
than an SQL string because they are already optimized and compiled. Using
SQL directly is best used when you have varying parameters and it is easier
to build an SQL string.
 
I have object myData which contains the SQL and I can access to its fields as
myData!fieldname
from your example I do not see where is the defined string and also do not
understand what is rst.name
rgs
 
Here we open the recordset. zztest is a stored query:
set rst = currentdb.OpenRecordset("zztest")

Here we are doing a DSum on a field named mar in the Recordset. The second
argument of the DSum function is the Domain Name. It expects the name of a
Domain as a string. In this case, the Domain is the open Recordset. We can
pass the name as a string using the Name property of the recordset.

?dsum("[mar]",rst.Name)

If you can post the code where you are opening your recordset, I will have a
look and perhaps offer a suggestion.
 
IF am not wrong you mean a sum of a particular field try this:
assumption
let recordset name be= rst
field to be summed up be= price
variable to hold totals be =total

total=0
do until rst.EOF
total=total+rst!price
rst.movenext
loop
debug.print total

the debug window should show the totals of the sample field "price"
 
Back
Top