Access 97 report format with calculated field

[Peter Rose]

Hi,
I have a database which contains data sets for several
sites. I've produced a report which has with the name of
the site, under it a list of the data for that site, under
that a sum for the data, then the next site and so on.

I want to change the colour of the name of each site
depending on the sum total associated with that site. Eg,
if the sum is greater than 100, that site name is changed
to green, if greater than 1000, it goes red!

Thanks in advance...Oh, not too good at Vis basic but will
give it a try!

 

[Duane Hookom]

You can add a text box in the group header bound to an expression like:
=Sum([data])
Name it txtSumOfData.

Then add code to the On Format event of the group header
Private Sub GroupHeader0_Format(Cancel As Integer, FormatCount As Integer)
If Me.txtSumOfData > 1000 Then
Me.txtSite.ForeColor = vbRed
Else
Me.txtSite.ForeColor = vbBlack
End If
End Sub

You could set additional colors using a Select Case structure.