Summing in subform textbox with criteria

[Sketchin]

Hi All,

I am trying to sum the values in a textbox on a subform using a textbox in that subforms footer. Currently I have the textbox's control source to be this: =Sum(IIf([chkInvoiceSent]=True,[txtPaymentAmount],0)). This isn't filtering the appropriate records and is instead summing everything.

I want to sum payment amount only if the chkinvoicesent box is checked off. Is the proper way to do this with a Dsum? I wanted to do it with the sum because it is faster and more elegant, or so I thought!

 

[pbaldy]

I would expect that to work. The field is a Yes/No field? Are those control names or field names (or both)?

 

[Sketchin]

So would I! Field is a yes/no, those are control names.

 

[Solo712]

Hi All,

I am trying to sum the values in a textbox on a subform using a textbox in that subforms footer. Currently I have the textbox's control source to be this: =Sum(IIf([chkInvoiceSent]=True,[txtPaymentAmount],0)). This isn't filtering the appropriate records and is instead summing everything.

I want to sum payment amount only if the chkinvoicesent box is checked off. Is the proper way to do this with a Dsum? I wanted to do it with the sum because it is faster and more elegant, or so I thought!

This is a strange one. It should work the way you have it set up. But then again I have never done a conditional on Sum() like this. I suspect it may not be working because in the the detail section the txtPaymentAmount carries a non-zero amount in every record. A quick way to test this would be to change the Control Source for the [txtPaymentAmount] in the detail i.e.

 =IIf([chkInvoiceSent]=True, [x]*[y] (or however you calculate the invoice amount), 0)

and in the footer do simply

=Sum([txtPaymentAmount]).

Best,
Jiri