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))

Using VLOOKUP function with datasource query in Excel

When using the VLOOKUP function on a table array with data returned from a data source query, I found it necessary to explicitly convert my lookup value to a text value. Failing to do so normally resulted in a error.


=VLOOKUP(TEXT(A1,0),Table_Query_from_My_Data_Source,2,FALSE)