Highlighting Maximum Value in a Row 1

[STRATMAN1]

I have an Access report based on a crosstab query, with rows as "month" and columns as "employee" with values as sales dollars for that month. I am trying to shade the highest value in each month row. The shading is not hard, but I dont know how to construct the code (have tried dmax function) when trying to find the max of column fields in each row. Have tried the dmax function, DMax("[joe]" "[sally], ... etc but cant figure out how to make an array out of the column field names. Perhaps there is an easier way. All good help appreciated. 1st reply was from dhookum, who asked for SQL view of my crosstab query:
HERE IS THE SQL VIEW OF MY CROSSTAB QUERY:
TRANSFORM Sum(TBLSALES.SALESDOLLARS) AS SumOfSALESDOLLARS
SELECT TBLSALES.MONTH
FROM TBLSALES
GROUP BY TBLSALES.MONTH
PIVOT TBLSALES.EMPLOYEE;

employees is the column header in the crosstab. All good advice appreciated.

 

[TheAceMan1]

How are ya STRATMAN1 . . .

First, backup the report so you can come back to square one if you have to. Then proceed with the following:
[ol][li]Open the report in [blue]design view[/blue].[/li]
[li]For the controls in the [blue]detail section[/blue] you wish to [blue]hilite[/blue], add a check mark [blue]?[/blue] to the [blue]Tag[/blue] property of each.[/li]
[li]Next, in the [blue]On Format[/blue] event of the [blue]Detail Section[/blue], copy/paste the following code:

[blue]   Dim hldNam As String, hldVal, ctl As Control
   
   [green]'Hold highest value & associated control name per record.[/green]
   For Each ctl In Me.Detail.Controls
      If ctl.Tag = "?" Then
         If ctl.Value > hldVal Then
            hldVal = ctl.Value
            hldNam = ctl.Name
         End If
      End If
   Next
   
   [green]'Color the record here.[/green]
   For Each ctl In Me.Detail.Controls
      If ctl.Tag = "?" Then
         If ctl.Name <> hldNam Then
            Me(ctl.Name).BackColor = vbWhite
         Else
            Me(ctl.Name).BackColor = vbGreen
         End If
      End If
   Next[/blue]

[/li][/ol]
[purple]Thats it . . . give it a whirl and let me know . . .[/purple]

See Ya! . . . . . .