locked
=Sumif([Fr Labor SUM],[FR Status]="Approved") RRS feed

  • Question

  • Is there a way to do a SUMIF statement as the control source of a field in the header of a form?  I could do this with a query in a subform, but I am trying to do this without adding a subform (one calculation).  The sum feature works, but the criteria throws me off.

    I need to sum the ['FR Labor Sum] if the [FR Status] = "Approved"  

    Note:  The form is [FR-Listing]

    Thank you,
    Mark Matzke


    • Edited by Mark Matzke Friday, August 1, 2014 3:43 PM Spelling
    Friday, August 1, 2014 3:42 PM

Answers

  • There are various expressions you could use, e.g.

    =Sum([FR Labor Sum]*IIf([FR Status] = "Approved",1,0))

    =Sum(IIf([FR Status] = "Approved",[FR Labor Sum],Null))

    Ken Sheridan, Stafford, England

    • Marked as answer by Mark Matzke Friday, August 1, 2014 4:32 PM
    Friday, August 1, 2014 3:58 PM

All replies

  • There are various expressions you could use, e.g.

    =Sum([FR Labor Sum]*IIf([FR Status] = "Approved",1,0))

    =Sum(IIf([FR Status] = "Approved",[FR Labor Sum],Null))

    Ken Sheridan, Stafford, England

    • Marked as answer by Mark Matzke Friday, August 1, 2014 4:32 PM
    Friday, August 1, 2014 3:58 PM
  • Good Morning Mark,

    As an example, here are formulas I have as the control source of a textboxes on Forms & Reports;

    =IIf([AccountingID] Is Null,Null,Nz(DLookUp("SumOfAGAMOUNT","FEEAG","AccountingID=" & [AccountingID]),0))

    =IIf([AdminClosed]=True,"Administratively Closed",Null)

    =IIf(IsNull([Non-ActionPEB]),Null,"Determined No Action Required " & [Non-ActionPeb])

    =IIf(IsNull([ApprovalDate]) And IsNull([ConditionedApprovalDate]) And IsNull([DisapprovalDate]) And IsNull([RemovedDate]) And IsNull([Non-ActionPEB]),"Pending",Null)

    So using these working examples you can do something like

    =IIf([FR Status] = "Approved", Sum(['FR Labor Sum]))

    However, I think either you are trying to add fields together that are on the Form in the which case you would simply do like;

    =Nz([RequiredFeesTextBox],0)+Nz([ManualFee],0)-Nz([Payment],0)

    But if you are referring to a sum of numbers associated with a single record on a sub data sheet then you will most likely have to have the subForm on your Form to get the data unless you use a Query for your Form and have a calculated Field in your Query as an alias.

    Example

    ([BalanceTestFees.SumOfTestFees]+[BalanceTrafficFees].[SumOfTrafficFees]+[BalanceManualFee].[SumOfManualFee]-[BalancePaymentInformation].[SumOfPayment]) AS SumofSums

    Hth


    Chris Ward

    Friday, August 1, 2014 4:11 PM
  • I see Ken has supplied some examples that are more concise.

    I would defer to Ken as he is more experienced than I.


    Chris Ward

    Friday, August 1, 2014 4:13 PM