Sum of all charges to a client minus payments

Question

Hello... I'd like to sum up all charges for a client and then subtract the total payments in an unbound form field.

This much works and shows the total charges for one record:

=IIf((IsNull([subAccountMgmt].[Form]![AcctAmount])),([subAccountMgmt].[Form]![AcctFees]),([subAccountMgmt].[Form]![AcctTotal]))

Then I tried the following, but I get an error:

=Sum(IIf((IsNull([subAccountMgmt].[Form]![AcctAmount])),([subAccountMgmt].[Form]![AcctFees]),([subAccountMgmt].[Form]![AcctTotal])))

After I get this part right, I can test the payment part, but I can't really do that until all the charges will add up. Any suggestions?  Thanks!

Answer 1

Hi,

Can you verify all records have values in AcctTotal? Thanks.

Answer 2

Yes they do. But I was trying to make it more bullet proof with the IsNull in there in case only the fees were applicable and no actual revenue exchanged hands. Without =Sum in there, clicking on each record changes the amount in the unbound box without showing "Error".

Answer 3

Unfortunately, I think the Sum() function gets confused when there are Nulls in the records. Try adding IsNull() or Nz() against all the fields in the equation/expression.

Hope it helps...

Answer 4

I made a couple of changes so that AcctTotal holds all charges. I have tried various alterations, but the error persists. The sum part won't even work when I put it in the header of the subform. If you have a better idea of the proper syntax, please let me know Thanks.

=Sum(Nz([subAccountMgmt].[Form]![AcctTotal]))

Answer 5

Hi 985Hunter,

What error do you get? Did you set these two statements in the Control Source of TextBox? Is this subAccountMgmt a sub form? If this is a subform, is this unbound form filed in main form or subform? Could you share us a screen shot about your issue through OneDrive and share us link here?

I tried to set these statements in a simple form, it works. I assume it is related with your form design.

Best Regards,

Edward

---

MSDN Community Support
Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact [e-mail].

Answer 6

I made a couple of changes so that AcctTotal holds all charges. I have tried various alterations, but the error persists. The sum part won't even work when I put it in the header of the subform. If you have a better idea of the proper syntax, please let me know Thanks.

=Sum(Nz([subAccountMgmt].[Form]![AcctTotal]))

Hi Hunter,

Unfortunately, it's not really a matter of syntax. The Sum() function is a bit complex, and any problems with any of the records could result in an Error message. Try experimenting with small sets of data first with no null values to see if it makes any difference.

Just my 2 cents...