Summing a recordset field

grenee

Registered User.
Local time
Today, 04:22
Joined
Mar 5, 2012
Messages
212
Good Day All,

In VBA summing a field of a table or query is simple. However I cant figure out a way to sum a field of a record set.

I tried the following extract but to no avail:
I would be grateful for the solution to by problem:

Code:
Private Sub Combo7_AfterUpdate()
 Dim curDatabase As Database
    Dim rst  As Recordset
    Dim strFilter As String
    Dim FirstString As String
   
 
      Set curDatabase = CurrentDb
         
        FirstString = "SELECT [Account General Journal].*, MonthAndYearNames.ID" _
        & " FROM MonthAndYearNames INNER JOIN [Account General Journal] ON MonthAndYearNames.MonthName = [Account General Journal].SpareType" _
        & " WHERE [Account General Journal].ACType = 1013 AND MonthAndYearNames.ID <= " & strFilter
        
        Set rst = curDatabase.OpenRecordset(FirstString)
        
        rst.MoveLast

       MsgBox DSum("Debit", "[rst]")
    
End Sub
 
Why are you even attempting to do it like this?
Scrap the recordset & do this

Code:
Private Sub Combo7_AfterUpdate()

Dim strSQL As String
strSQL = "SELECT [Account General Journal].*, MonthAndYearNames.ID" _
        & " FROM MonthAndYearNames INNER JOIN [Account General Journal] ON MonthAndYearNames.MonthName = [Account General Journal].SpareType" _
        & " WHERE [Account General Journal].ACType = 1013 AND MonthAndYearNames.ID <= " & strFilter

 MsgBox DSum("Debit", strSQL)

End Sub
 
Thanks for your quick response.

However it is throwing the following error message:

" The field is too small to accept the amount of data you attempted to add. Try inserting or adding less Data"
 
Wasn't aware you could use an SQL statement like that, but this should help:

http://www.baldyweb.com/ImmediateWindow.htm

I don't see you giving a value to strFilter.

Oops - you can't - it must be a table or query - not sure what (or if) I was thinking...Apologies

You need to create a query with this as the query SQL
Code:
"SELECT [Account General Journal].*, MonthAndYearNames.ID" _
        & " FROM MonthAndYearNames INNER JOIN [Account General Journal] ON MonthAndYearNames.MonthName = [Account General Journal].SpareType" _
        & " WHERE [Account General Journal].ACType = 1013 AND MonthAndYearNames.ID <= " & strFilter

Call it e.g. qryAccountFilter

As Paul has pointed out, you need to define strFilter value

Then use DSum("Debit","qryAccountFilter")

"The field is too small to accept the amount of data you attempted to add. Try inserting or adding less Data"
If that error occurs again, change the datatype of the Debit field
e.g. from Integer to Long Integer or possibly from Single to Double
 
You could continue with your recordset by switching to a totals query:

Code:
FirstString = "SELECT Sum(Debit) As TotalDebits FROM...WHERE..."

Set rst = curDatabase.OpenRecordset(FirstString)
        
MsgBox rst!TotalDebits
 

Users who are viewing this thread

Back
Top Bottom