totn Access

MS Access 2003: Conditionally format a field in a report

This MSAccess tutorial explains how to conditionally format a field in a report in Access 2003 (with screenshots and step-by-step instructions).

Question: In Microsoft Access 2003/XP/2000/97, I have a database and I want to change the Background color of a field on a Report based on the value. Is this possible?

Answer: We've created a sample Access database that demonstrates how to apply a conditional format to a report in Access97.

Download Access 97 version

We've created a report called "rptSuppliers" which displays the supplier_id, supplier_name, and supplier_city fields. We want to change the background color of the supplier_name if its value is 'IBM'. In this example, we'll set the background color to red if the value is IBM, otherwise the background color will show as white.

To do this, open the Report in Design View and click on the Detail bar.; Right-click (while over the Detail bar) and select Properties.

Microsoft Access

We've placed code on the "On Format" event.

Microsoft Access

The code looks as follows:

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)

   'Set the backstyle to normal (default is usually transparent)
   supplier_name.BackStyle = 1

   If supplier_name = "IBM" Then
      supplier_name.BackColor = vbRed

   Else
      supplier_name.BackColor = vbWhite

   End If

End Sub

Now when you view the report, it will look as follows:

Microsoft Access

As you can see, the background color is set to red whenever a supplier_name of IBM is encountered.

Share on: