Using Conditional Formatting on an Exam Results Report

Conditional Formatting may be applied to Text Box and Combo Box controls on Access Forms and Reports.   It allows us to format the data contained in a particular Text or Combo Box, according to value of that same data.  For example we can create Conditional Formatting for a text box to display a currency value, which when less than 0, will display that value in a red font, instead of the normal black.

Moreover, as well as formatting the Text or Combo Box according to the value contained in that same control, we can also format the Text or Combo box according to the value contained in another field of the same record.  So, for example, we can display a Text Box containing a CompanyName in bold font if the value of its AnnualTurnover field is greater than or equal to, say,  £100,000.  The great thing is, the data contained in the AnnualTurnover field does not necessarily have to be displayed on the form or report, as long as it is stored in the form or reports underlying Record Source.  In order to do this, we would enter an expression for the condition.

So let's take a look at how we go about applying Conditional Formatting using the example of a Student Exam Results Report.  The report applies conditional formatting based on the students exam result.  Students records with a result above 74 % are displayed in green, and those below 40 %, in red. The formatting for those in between is set to blue by default. To enhance clarity, I have also applied a sort on the Results field, thereby ordering the records from highest result to lowest. If you wish, you can download the complete solution by clicking the link below:

Student Exam Results

Our example database contains a table, tblExamResults, and report, rptExamResults- the table being the Record Source for the report.  Our table contains a list of student names and their corresponding exam results:

Figure 1: tblExamResults - The Record Source for our Report.

The rptExamResults report is based on this table.  I created it by clicking the REPORT icon (located in the REPORTS group of the CREATE ribbon) whilst tblExamResults was highlighted in the NAVIGATION PANE.  I then just needed to tidy it up in DESIGN VIEW, and add the Conditional Formatting (on each text box on the report) in addition to a SORT on the Results field.  This is the finished result:

Applying Conditional Formatting.

To begin with, lets look at how we apply Conditional Formatting to the Result field of rptExamResults.

1. Open the report in DESIGN VIEW
2. Right click the Result Text Box.
3. Click CONDITIONAL FORMATTING from the drop down menu.
4. When the CONDITIONAL FORMATTING dialog form opens, set the conditions as described in the next stage of the process below.

Figure 2: The Conditional Formatting dialog form.  It is possible to have a maximum of
three different conditions plus the default formatting.  

Setting Conditions based on FIELD VALUE IS

The next stage is to set the actual conditions for the Result field of the report (see Figure 2 above). NB: when FIELD VALUE IS is selected, it refers to the value contained in the text box being formatted:

1. First set the DEFAULT FORMATTING section so it displays a BLUE FONT.  Do this by selecting BLUE from the FONT/FORE COLOR icon
2. Next set CONDITION 1 so that a BOLD GREEN FONT displays when the  FIELD VALUE IS GREATER THAN 74.  If necessary click the drop down list to select FIELD VALUE IS, and then do the same in the next combo box along to select GREATER THAN.  Then just enter the value 74 in the third box along, and select the BOLD and RED icons.  You should see a preview in the box below when done.
3. Click the ADD >> button to show CONDITION 2.
4. Repeat the process of stage 2 so that a BOLD RED FONT displays when FIELD VALUE IS LESS THAN 40.
5. Click OK to close.

Setting Conditions based on EXPRESSION IS

The next stage is to set the conditions for the StudentId field.  Since we are not basing the condition on the value contained in the StudentId field itself, we need to construct an expression which refers to the value contained in the Results field. As such, we shall be selecting EXPRESSION IS from the appropriate drop down lists, rather than FIELD VALUE IS.

1. Again, set the DEFAULT FORMATTING section so it displays a BLUE FONT.  Do this by selecting BLUE from the FONT/FORE COLOR icon
2. Next set CONDITION 1 so that a BOLD GREEN FONT displays when the EXPRESSION IS [Results]>=75.  Do this by clicking the drop down list to select EXPRESSION IS, and then enter the expression in the elongated box (see Figure 3 below).  Then select the BOLD and RED icons as before.  
3. Click the ADD >> button to show CONDITION 2.
4. Repeat the process of stage 2 so that a BOLD RED FONT displays when EXPRESSION IS [Results]<40
5. Click OK to close.

Figure 3: Condition based on EXPRESSION IS.  Note the elongated box where the expression is entered.  This appears when EXPRESSION IS is selected from the drop down list.

Finish the report by repeating this last stage for the FirstName and Surname fields using the EXPRESSION IS selection when creating the conditions.