databasedev.co.uk - database solutions and downloads for microsoft access

Create Better MS Access Reports
Report Builder for Microsoft Access helps you create dynamic, professional-looking reports fast! The easy-to-understand wizard helps you with complex tasks like calculated fields, adding subreports, customizing styles, as well as grouping and sorting. Download a free trial today!

Microsoft Access Expressions

Counting field values in a Database Report using the Count and IIF expressions

If you store values in your database to indicate choices for your uses to make, such as storing values like Yes/No, True/False, Male/Female, Adult/Child, there may be times when you would like to make calculations on this data. You can do this using the Count and IIF expressions.

If we take the following table, that stores data relating to Employees, you will see that one of the fields stores a value to indicate whether the Employee is Male or Female:

tblEmployees
FirstName LastName Gender BirthDate
Nancy Davolio F 08/12/1968
Andrew Fuller M 19/02/1952
Janet Leverling F 30/08/1963
Margaret Peacock F 19/09/1958
Steven Buchanan M 04/03/1955
Michael Suyama M 02/07/1963
Robert King M 29/05/1960
Laura Callahan F 09/01/1958
Anne Dodsworth F 02/07/1969

In this example, you can see that we store a single text value to indicate whether the Employee is Male (M) or Female (F).

Now if we use this information in a Microsoft Access report, we may want to calculate how many Male/Female employees that we have in our records.

We can do this by adding a couple of Unbound text boxes into the Report Footer that use the Count and IIf functions. We need to add one that counts if the records contain a value in the gender field of M and one that counts if the records contain a value in the gender field of F. We use the following expressions to do this:

 =Count(IIf([Gender]="M",0))

 =Count(IIf([gender]="F",0))

The design of the report, including the two unbound text boxes with the Count and IIf functions.

When we preview the report, we will now see the results, that give us a count of how many male employees and how many female employees are recorded in the database:

The completed report, detailing the totals derived from using the Count and IIf expressions in the report design.