Nested Sum IIF

[cfshelley]

I need to revise the following formula to include one extra IIF.

=Sum(IIf(Now()-[SCHED_CMPL_DT]<30,1,0))

This will total the number of records that are less than 30 days old.

I want to qualify the statement so that it sums only records with the Facility ID Number of "20". So I came up with this incorrect formula and can't figure out how to fix it.
=Sum(IIF([FACIL_ID_NB]="20" and (IIf(Now()-[SCHED_CMPL_DT]<30,1,0)))

I see there are posts out there encouraging folks to write functions rather than nested iifs. I don't have a clue how to start on that, but I'm willing to learn!

Any assistance will be greatly appreciated.
Thanks, Christine

 

[dhookom]

Try:

=Sum(Abs([FACIL_ID_NB]="20" and (Date()-[SCHED_CMPL_DT]) <30))


Duane
Hook'D on Access
MS Access MVP

 

[cfshelley]

That's perfect! Thank you for your quick response.
Christine