Conditionally format a field in a report in Access 2003/XP/2000/97

Question: In 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.


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


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:

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