Sum(Iff() in report

Question

I'm working on a report and would like to have a summary in the report footer for each section of the report.

The summary will include tickets open <30 days

In the report I've created a textbox and am using the below as the Control Source.

=Sum(IIf([qryDMIncidents-Client-ACTIVE-PENDINGCOLUMBIA].[Days Old]<30,1,0))

This will work in a query as an expression, however it does not work in the Control Source Expression Builder. Why? I do not know?

Answer 1

Your approach should work if you reference a control on the report instead of a field in its record source.

For example, if the report design includes a text box named txtDaysOld whose Control Source is [Days Old], use this ...

=Sum(IIf([txtDaysOld]<30,1,0))

If the report doesn't already include a similar text box, add one. And the text box does not need to be visible for that =Sum() expression to work.