locked
VBA for Access Sum a field RRS feed

  • Question

  • I have a subform that displays various payments made by the customer.  I'm trying to add the payments together to get the total that the customer has paid.  Caveat - I only want to add payments that have not been returned or chargedback.  I've tried two different methods and I'm still having difficulty.  Here are my examples.

    Customer pays $100.00 on January 1st, $100 on Feb 1st, $100 on March 1 (The March payment is returned NSF), customer pays $360 on April 1st.  The total for this customer should be  3 of 4 pmts total $560 paid.  My database is coming up with $400.  So four payments of the $100. 

    Any status above status 2 is a return of some form.

    I tried 

           TxtPmtTTL.Value = (DSum([PmtAmt], "Qry_PmtsV", "[PmtStatus] <= 2" & "And [PlanID] =" & Me.PlanID))

    And I've tried

    PmtsList = "SELECT * From Qry_PmtsV WHERE (((Tbl4_Pmts.PlanID)=" & Me.PlanID & ") AND ((Tbl4_Pmts.PmtStatus)<=2));"

    Set db = CurrentDb
    Set rs = db.OpenRecordset(PmtsList)
    addme = rs.Fields("PmtAmt")
    total = 0

    For X = 0 To rs.RecordCount - 1

    total = total + addme
    rs.MoveNext
    Next X

    rs.Close
    Set rs = Nothing
    db.Close 

    The planID is how I identify the customer.  I don't understand why the system is only looking at the first payment amount and not looking at each individual record and adding the payments.  Any help would be greatly appreciated.  I'm a pretty basic, just see some code and modify it type of programmer. I literally google what I need and modify it but I can't see what I'm doing wrong here.

    Thanks again

                 
    • Edited by patientv Wednesday, June 17, 2015 8:15 PM
    Wednesday, June 17, 2015 8:14 PM