Expression Limit on Sum IIf In Method ? Used in Report Calculation

[JimStrand]

I'm using an expression in Report field which aggregates 13 departments.

I added one dept at a time to make sure this method was working. When I added the 13th dept my calculation returns #error.

=(Sum((IIf([ModeName] In ("Admin excl IT","IT","Police","Mobility","Core Support excl Fac. Maint","Facilities Maintenance","Rail MOW","Light Rail Maintenance","Light Rail Transportation","Metro Maintenance","Metro Transportation","Bus Maintenance","Bus Transportation"),IIf([CYBudPY]="PY",([Amount]),0),Null)))/[Forms]![checkmax]![WeekNumber])

Is there a wildcard character I can use to select ALL Departments? I tried using "*" but that did not work. Any suggestions would be appreciated.

 

[dhookom]

I would add a field to the department table that could be used as the selection/inclusion criteria. I don't care for hard-coding values into expressions particularly when there are so many values and the IN values approach 256 characters.

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016