none
sum total of a recordset RRS feed

  • Question

  • Hi all,

     

    how to sum the total of a column using VBA programming? The record will be from database (using ADO)

    Thanks

     

     

    Friday, November 25, 2011 12:24 PM

Answers

  • Hi,

    Use a loop.

         total = 0

         for each record in the record set

                total = total + records value from column

     actually is there a for each in VBA.... you might need to do for index = 0 to number of records in recordset


    "The programmer, like the poet, works only slightly removed from pure thought-stuff. He builds his castles in the air, from air, creating by exertion of the imagination." - Fred Brooks
    • Edited by Derek Smyth Friday, November 25, 2011 1:03 PM
    • Marked as answer by Didier_2000 Sunday, November 27, 2011 5:31 AM
    Friday, November 25, 2011 1:02 PM

All replies

  • Hi,

    Use a loop.

         total = 0

         for each record in the record set

                total = total + records value from column

     actually is there a for each in VBA.... you might need to do for index = 0 to number of records in recordset


    "The programmer, like the poet, works only slightly removed from pure thought-stuff. He builds his castles in the air, from air, creating by exertion of the imagination." - Fred Brooks
    • Edited by Derek Smyth Friday, November 25, 2011 1:03 PM
    • Marked as answer by Didier_2000 Sunday, November 27, 2011 5:31 AM
    Friday, November 25, 2011 1:02 PM
  • Can i have the exact coding for VBA for this? As i'm newbie in VBA programming..
    Saturday, November 26, 2011 2:37 AM
  • Then I would look at all the examples you can find in MSDN then at www.msdn.microsoft.com/ and search for VBA ADO Recodset etc.

    You can also enter teh SQL Code:

    SELECT SUM(FieldName) as FieldName FROM [TableName];

     

    This will return one field in one row called FieldName which will be teh sum of all records for that field.

     


    Rod Gill

    The one and only Project VBA Book Rod Gill Project Management
    • Edited by Rod Gill Saturday, November 26, 2011 4:26 AM
    • Proposed as answer by Rod Gill Saturday, November 26, 2011 4:27 AM
    Saturday, November 26, 2011 4:22 AM
  • The VBA loop that I use with a recordset object is:

    dim dSum as double

    dSum = 0

    Do While Not rs.EOF

         dSum = dSum + rs.Fields(enter the index of your column here)

         rs.MoveNext

    Loop

    • Proposed as answer by tyson scott Wednesday, May 15, 2019 9:42 PM
    Wednesday, May 15, 2019 9:41 PM