All Things Code

Recently I updated a few MS Access reports that used various grouping; by day, by week and by month. In the group footers I needed to add two textboxes to display visit counts by campus.  Below is the report, group by week.

To show the first day of each week, the following expression was placed in the group header:

=Format([Time_In]-Weekday([Time_In],2),"dd-mmm-yyyy")

To show the count for the campuses, I included the following expressions:

=Count(IIf([Campus]="Pecos" Or IsNull([Campus]),1,Null))
and
=Count(IIf([Campus]="Williams",1,Null))

I look for null value because the Williams campus is a recent addition and there exists records where campus is null. I could have also summed the results as with the following expression:

=Sum(IIf([Campus]="Pecos" Or IsNull([Campus]),1,0))