Getting Totals with Recordsets

  • Thread starter Thread starter Michael Conroy
  • Start date Start date
M

Michael Conroy

I might be overthinking this, but I want to pull up the payments made between
two dates and add those payments together to get a total. Rather than
building two queries, I would like to do this within my code using recordsets
and maybe a sub query, unless someone can suggest a better way of doing it.
An important feature is that there might be more than one payment between the
dates. (Imagine paying a credit card bill with $50 every week, after four
weeks the total I want is $200) I can get a recordset to pull up the payments
between the dates, but I don't know how to embed the recordset around a sum
function.

My code is as follows:
strSQL = "SELECT tblPayment.PayDate, tblPayment.Payment " & _
"FROM tblPayment" & _
"WHERE (((tblPayment.PayDate)>#" & PD & "# And
(tblPayment.PayDate)<#" & RD & "#));"

Set rstTemp = dbs.OpenRecordset(strSQL, dbOpenSnapshot)

So now I have a recordset with four records in it, and I want to sum them.
Any help would be appreciated.
 
On Mon, 10 Aug 2009 18:00:01 -0700, Michael Conroy

A Totals query would be my first choice. If you don't want that, a
select statement with a Sum clause would be a decent alternative:
select Sum(Payment)
from tblPayment
where <etc.>

A (imho) braindead solution would be to loop over the recordset and
sum up the total:
dim curTotal as Currency
while not rstTemp.EOF
curTotal = curTotal + rstTemp.Payment
rstTemp.MoveNext
wend

-Tom.
Microsoft Access MVP
 
Back
Top