Alternate Shading of Report Records

[illini]

I have a report which groups records by a time-event. I would like to alternate shading of the records by grouping the records which share the same time-event. Therefore, all records with "5:00 am" will be shaded gray. All records with "5:05 am" will be shaded white. And so forth...

What would be the best approach to this? -illini

 

[SgtJarrow]

The below code will shade every other line in a report...you could modify it if you like to say...if the time ends in 05, 15, 25, 35, 45, and 55 make it grey...otherwise, make it white....let me know if you need some help with this...

Insert into Detail.OnFormat Property of Report
********************************************************
Const cLightGrey = 12632256
Const cWhite = 16777215

If Me.Detail.BackColor = cWhite Then
Me.Detail.BackColor = cLightGrey
Else
Me.Detail.BackColor = cWhite
End If
If we knew what it was we were doing, it would not be called research, would it? - Albert Einstein

Robert L. Johnson III, A+, Network+, MCP
Access Developer/Programmer
robert.l.johnson.iii@citigroup.com

 

[oharab]

If you have Access 2000 or above you can use conditional formatting.
It won't work on the background, but if you put a flat textbox behind everything, you can cheat a little!

HTH

Ben ----------------------------------------
Ben O'Hara

http://www.RobotParade.co.uk

----------------------------------------

 

[illini]

Thanks Robert.

I imported your code as instructed and it worked as described. Then, I modified it to only alternate a specific text box based on the time event. Here's what I have so far...

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
Const cLightGrey = 12632256
Const cWhite = 16777215
Dim x As String

X = Me.Effective_Time
If Me.Effective_Time = X Then
Me.Detail.BackColor = cWhite
Else
Me.Detail.BackColor = cLightGrey
X = Me.Effective_Time
End If
End Sub

This changes only the Text_Box named 'Effective_Time'. Unfortunately, the variable 'X' continues to change as each record is added to the report. Therefore, all of the records have a white background. Do you have any ideas? -illini

 

[SgtJarrow]

I would expect the problem to be in the defining of X as a string....define it as a date instead and see what happens.....you are comparing a date field box with a string value and they will not be the same.



Dim X As Date If we knew what it was we were doing, it would not be called research, would it? - Albert Einstein

Robert L. Johnson III, A+, Network+, MCP
Access Developer/Programmer
robert.l.johnson.iii@citigroup.com

 

[illini]

My problem was setting X = Me.Effective_Time because this Detail.OnFormat event happens for each record. I modified the code as follows...

[tt]Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
Const cLightGrey = 14079702
Const cWhite = 16777215
'X starts off equal to "".
If Me.Effective_Time = X Then
Me.Detail.BackColor = cWhite
Else
Me.Detail.BackColor = cLightGrey
'The following line will only affect the first row.
If X = "" Then Me.Detail.BackColor = cWhite
X = Me.Effective_Time
End If
End Sub[/tt] -illini

 

[illini]

Slight Modification...

[tt]Public X as String
Public xColor as string

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
Const cLightGrey = 14079702
Const cWhite = 16777215

If Me.Effective_Time = X Then
Me.Detail.BackColor = xColor
Else
If Me.Detail.BackColor = cWhite Then xColor = cLightGrey Else xColor = cWhite
Me.Detail.BackColor = xColor
If X = "" Then
Me.Detail.BackColor = cWhite
xColor = cWhite
End If
X = Me.Effective_Time
End If

End Sub[/tt] -illini

 

[MichaelRed]

What is previously posted includes som (un-necessary) logic, Consider the following (as the original post) which pplies to alternating each line in the report, so the changes to band the time still need to be applied.

Still, even these could (perhaps) be simplified by using the 'partition' function in the source query to generate a banding value.

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)

    'Michael Red.   2/13/2002.      To 'Toggle the backolor, which
    'Provides alternate line shading
    Const XorToggle = 4144959

    Me.Detail.BackColor = Me.Detail.BackColor Xor XorToggle

End Sub
[code]
 MichaelRed
m.red@att.net

There is never time to do it right but there is always time to do it over