 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / New Users / July 2006sum of field in recordset
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.  SignatureDoug Steele, Microsoft Access MVP http://I.Am/DougSteele (no e-mails, please!) > Hallo > Is there please some easy function (maybe DSUM or SUM or whatever) which is [quoted text clipped - 14 lines] > Thanks > Ari 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. > 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 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? SignatureDoug Steele, Microsoft Access MVP http://I.Am/DougSteele (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. [quoted text clipped - 5 lines] > > Thanks > > Ari http://www.accessmonster.com/Uwe/Forums.aspx/access-gettingstarted/200607/1 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 > 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? [quoted text clipped - 14 lines] > > > > http://www.accessmonster.com/Uwe/Forums.aspx/access-gettingstarted/200607/1 Really! I did not know that. SignatureDoug Steele, Microsoft Access MVP http://I.Am/DougSteele (no e-mails, please!) > 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 [quoted text clipped - 19 lines] > > > > Thanks > > > > Ari http://www.accessmonster.com/Uwe/Forums.aspx/access-gettingstarted/200607/1 I did not test it in a running VBA procedure, only the immediate window. I don't know that there should be a difference. > Really! I did not know that. > [quoted text clipped - 29 lines] > > > > http://www.accessmonster.com/Uwe/Forums.aspx/access-gettingstarted/200607/1 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. > 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, [quoted text clipped - 3 lines] > plenty of them in my application. > Thanks 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. > 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 |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.