Formatting a header

R

ReportSmith

I've looked at several posts, but think I need additional information...

I've built a report on a crosstab query (months across the top). I've
gotten the report to a level where I would like to format the appropriate
month header in the report based on the current month.

So for example, if the current month is Jun, I would like to hi-light the
Jun header (I can play with the detail section later).

Where would I place the IF or CASE statement (which event property)?

Thanks in advance for any pointers.
 
M

Maurice

This will be possible if you have named your fields appropriate so you can
refer to them. The place to place the code I'd think is the On_Format of the
report header. You could then do a comparison on the month extracted from the
Date and compare this with your fields.

hth
 
R

ReportSmith

Maurice,
Thanks for the response. I've tried that previously - I didn't get the
results I expected - there was no shading at all. The code looks like this:

Private Sub Report_Open(Cancel As Integer)
If Format(Month(Now()), "mmm") = "Jun" Then
Jun_Label.BackColor = 13882323 '=Light Gray
End If
End Sub

This was only to test the code and nothing happened when I ran it (opened
the report in preview mode).
 
D

Duane Hookom

Format(Month(Now()), "mmm") will return Dec in January and Jan in all other
months.

Month(Now()) will return values 1 - 12.
Format(1-12, "mmm") will return Dec or Jan.

Remove the Month() function so you end up with only:
If Format(Now(), "mmm") = "Jun" Then
or
If Month(Now()) = 6 Then
 
R

ReportSmith

Thanks Duane,
It makes sense. I have the detail section already hi-lighted using the code
below...

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

curmonth = Month(Now())

Select Case curmonth '8454143 = Yellow
Case 1
Jan.BackColor = 8454143
Case 2
Feb.BackColor = 8454143
Case 3
Mar.BackColor = 8454143
Case 4
Apr.BackColor = 8454143
Case 5
May.BackColor = 8454143
Case 6
Jun.BackColor = 8454143
Case 7
Jul.BackColor = 8454143
Case 8
Aug.BackColor = 8454143
Case 9
Sep.BackColor = 8454143
Case 10
Oct.BackColor = 8454143
Case 11
Nov.BackColor = 8454143
Case 12
Dec.BackColor = 8454143
End Select

End Sub

This code is in the right place for the detail, but when I tried out your
suggestion in the date header, I don't get any shading (?) Am I placing it
in the right section? The header code is as follows:

Private Sub GroupHeader0_Format(Cancel As Integer, FormatCount As Integer)
If Format(Now(), "mmm") = "Jun" Then
Jun_Label.BackColor = 13882323 '=Light Gray
End If
End Sub

I even tried putting the code in the Report On Open event...still nothing.
(Once this code works, I'l switch it to a CASE statement like the code above)

Thanks again.
 
D

Duane Hookom

The code should be running in the section containing the controls you want
to change.
You should be able to replace all of your Select Case with 1 line of code:

Me(Format(Date,"mmm")).BackColor = 8454143
 
Top