Formatting a header

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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.
 
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
 
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).
 
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
 
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.
 
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
 
Back
Top