sum total of a recordset

• 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

• 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 Friday, November 25, 2011 1:03 PM
• Marked as answer by 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 Friday, November 25, 2011 1:03 PM
• Marked as answer by 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 Saturday, November 26, 2011 4:26 AM
• Proposed as answer by 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 Wednesday, May 15, 2019 9:42 PM
Wednesday, May 15, 2019 9:41 PM