Conditional formatting in a report text box control 1

[STRATMAN1]

I have a control tied to a query that in a report returns several values. I am trying to use conditional formatting to shade the highest value in the field. I have used bracketed expressions, not field values, to accomplish this. I understand from Access help that the conditional formatting expression must result in a true or false condition. I am asking it to compare field values and cant write a statement using dmax (or other) function that returns a true/false result. Can I write a function like this and if so what would it be? All good advice appreciated!!!

 

[CautionMP]

Here is a sample, this is the entire code module for my report

Option Compare Database
Option Explicit
[green]'Global declaration to hold the max value[/green]
Dim lngMax As Long

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
[green]'This will be run everytime a record in the detail section
'is rendered on the report. You can use it to change the 
'formatting of the detail, a field, text...[/green]
  If Me.lngRecord = lngMax Then 'Check the record data
    [green]'Make the control BackColor grey because I am the
    'the max value[/green]
    Me.Controls("lngRecord").BackColor = 12632256
  Else
    [green]'Reset the BackColor to the default because I don't
    'match[/green]
    Me.Controls("lngRecord").BackColor = 16777215
  End If
End Sub

Private Sub Report_Open(Cancel As Integer)
  [green]'Use the DMax() function to find the max value[/green]
  lngMax = DMax("lngRecord", "tblColumnWrap")
End Sub

A note and couple of quick thoughts:[ul]
[li][tt]lngRecord[/tt] & [tt]tblColumnWrap[/tt] will need to be changed to match your data.[/li]
[li][tt]Detail_Format()[/tt] Can be used to change anything that shows up the in Properties window.[/li]
[li]Another way to do this is to toggle the BackStyle from Transparent to Noremal.[/li]
[li][tt]Me.Controls("blahblah")...[/tt] is used to address the default Access convention for naming controls the same as fields.[/li][/ul]
Hope this helps,
CMP


Instant programmer, just add coffee.