sum of field in recordset

  • Thread starter Ari via AccessMonster.com
  • 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
 
Ad

Advertisements

D

Douglas J Steele

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.
 
A

arista via AccessMonster.com

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
 
G

Guest

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

Douglas J Steele

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

Guest

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
 
Ad

Advertisements

D

Douglas J Steele

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
 
G

Guest

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
 
A

arista via AccessMonster.com

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
 
G

Guest

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.
 
A

arista via AccessMonster.com

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
 
Ad

Advertisements

G

Guest

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.
 
Ad

Advertisements

Joined
Apr 18, 2015
Messages
1
Reaction score
0
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"
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads


Top