DSum Criteria Question

[Geodon7]

What criteria would I use to sum all payments made on a single invoice? Each payment has it's own ID (PaymentID) and each invoice has it's own ID (InvoiceID). I need to have the criteria sum the payments relating to an individual InvoiceID. Any suggestions would help! Thanks everyone.

 

[GoDawgs]

=Dsum("[payments]","tablename","[invoiceid]= "&[invoiceid])

That will sum all the "payments" from tablename where the invoice ID is equal to the form's current record's invoice id. Hope that helps.

Kevin

 

[gol4]

if passing manually
dsum("paymentdollarsfield","thetablename","invoiceid = 4234&quot

if via a form
dsum("paymentdollarsfield","thetablename","invoiceid = " & forms!formname!invoicefield)

I see no need to filter out paymentid

you realise you need to replace the fields above with your DBs names

 

[Geodon7]

Thanks for getting back to me so quickly. I used the code that you provided me. (gol4: it is via a form). I will paste my code below. For some reason I get an "Invalid use of Null" error when trying to update the PaymentAmount in the subform. Any suggestions as to why? Thanks!

Here's the code:

Private Sub PaymentAmount_AfterUpdate()
    
    Dim totalpaid As Currency
    totalpaid = DSum("[PaymentAmount]", "Payment", "[FinConID]= " & [FinConID])
    TotalText.Value = totalpaid
    
End Sub

Thanks again.

 

[GoDawgs]

What's coming up null? is it the finconid?

 

[Geodon7]

It is the FinConID that is coming up Null. If I hit "End" instead of "Debug", and then enter more records, the sum works, so it's only for the first entry that it comes up Null. Do you know how I could avoid this?

 

[GoDawgs]

How bout throwing a null check around the dsum...like so...

if [finconid]<>&quot;&quot; then '(or is not null)

totalpaid = DSum(&quot;[PaymentAmount]&quot;, &quot;Payment&quot;, &quot;[FinConID]= &quot; & [FinConID])

end if

 

[gol4]

Excellent suggestion by Godawgs.

One other thing to consider is if it is working on all the others records is PaymentAmount getting focus before FinConID has a value set
sometimes moving the field ahead in the tab order will solve this as well or also Note: (from Access help)

Unsaved changes to records in domain aren't included when you use this function. If you want the DLookup function to be based on the changed values, you must first save the changes by clicking Save Record on the File menu, moving the focus to another record, or by using the Update method.