none
Sum of all charges to a client minus payments RRS feed

  • 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!

    Monday, October 31, 2016 9:04 PM

Answers

  • 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...

    • Marked as answer by 985Hunter Tuesday, November 1, 2016 7:01 PM
    Tuesday, November 1, 2016 2:59 PM

All replies

  • Hi,

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

    Monday, October 31, 2016 9:21 PM
  • 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".
    Monday, October 31, 2016 9:49 PM
  • 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...

    Monday, October 31, 2016 10:05 PM
  • 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]))


    • Edited by 985Hunter Tuesday, November 1, 2016 12:07 AM refreshed info
    Monday, October 31, 2016 11:17 PM
  • 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 MSDNFSF@microsoft.com.

    Tuesday, November 1, 2016 6:15 AM
  • 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...

    • Marked as answer by 985Hunter Tuesday, November 1, 2016 7:01 PM
    Tuesday, November 1, 2016 2:59 PM