Conditional format in Report

  • Thread starter Thread starter Susie K
  • Start date Start date
S

Susie K

Hi,

I'd like to use conditional formatting in a report to highlight the min and
max number of a field.

I can do it for the whole report, but how do I get it to give me the min and
max in each section?

Thanks!
Sue
 
I assume your sections are based on some groupings, is that true?
Don't know if this is the best, or easiest way, but here is what I have done
in the past.

1. Create private variables that are declared in the reports declaration
section:

Private intSecMin as integer, intSecMax as integer

2. Create two function that you can call from conditional formatting. You
cannot use a variable to test in conditional formatting, but if your function
returns the value of that variable, you can use it:

Private Function fnSectionMin() as integer
fnSectionMin = intSecMin
end sub

Private Function fnSectionMax() as integer
fnSectionMax = intSecMax
End sub

3. In the format event of the Section, use the DMin( ) and DMax( )
functions to get the value of the minimum or maximum of whatever field it is
you are looking for, that over the group of fields that match the group
(section). Assuming that you are grouping on Field1, then it might look like:

Private Sub GroupHeader0_Format(Cancel as Integer, FormatCount as Integer)

Dim strCriteria as string
strCriteria = "[Field1] = " & me.txt_Field1
intSecMin = DMIN("SomeField", "yourReportsQuery", strCriteria)
intSecMax = DMAX("SomeField", "yourReportsQuery", strCriteria)

End Sub

4. Finally, in your conditional formatting, set the properties like:

Expression IS
([Field1] = fnSectionMin()) = True

then set your format conditions.
 
Back
Top