Conditional Formatting - Make Each Month's Results a different col

  • Thread starter Thread starter Richard Horne
  • Start date Start date
R

Richard Horne

I have a report that lists various orders for the last few months - with some
as far back as a couple of years. I want to format the report so that each
month has text of a certain colour, i.e. all orders from January of any year
are red, Feb blue, March orange etc.

How can I achieve this?
 
Set up a table with 2 fields named MonthNumber and ColorNumber.
Both of type Number. Make MonthNumber the primary key.
Enter 12 records: 1-12 for the months (Jan-Dec), and the RGB value for the
color you want for that month.

Create a query using your table.
In a fresh column of the Field row in query design, enter:
MonthNumber: Month([InvoiceDate])
substituting your field name for InvoiceDate.
Save this query. Close.

Create another query using the query you just created as an input "table",
and also the table that has the color numbers for each month. Join the
MonthNumber field in the query to the MonthNumber field in the table. Add
the ColorNumber to the query's fields. Save this query.

Open your report in design view.
Set its RecordSource to the second query.
Add the ColorNumber to the detail section.
(Set its Visible property to No if you wish.)

Set the On Format property of the Detail section to:
[Event Procedure]
Click the Build button (...) beside this.
Access opens the code window.
Set up the code like this:

Private Sub Detail_Format(...
Dim lngForeColor As Long
lngForeColor = Nz(Me.ColorNumber, vbBlack)
Me.Text0.ForeColor = lngForeColor
Me.Text2.ForeColor = lngForeColor
'etc for other fields.
End Sub
 
Richard said:
I have a report that lists various orders for the last few months - with some
as far back as a couple of years. I want to format the report so that each
month has text of a certain colour, i.e. all orders from January of any year
are red, Feb blue, March orange etc.
Click to expand...


Create a table (named MonthColors) with two fields:
MonthNum Integer
ColorCode Long
and populate it with 12 records like:
1 255
2 16711680
3 65280
...

The change the report's record source query to something
like:
SELECT yourtable.*, MonthColors.ColorCode
FROM yourtable INNER JOIN MonthColors
ON Month(yourtable.thedatefield) = MonthColors.MonthNum

(Note that kind of Join can not be used in query design view
so stay in SQL view)

With all that taken care of, add a text box to the report
and bind it to the ColorCode field. Now you can use code in
the report's Detail section's Format event procedure to set
the section's back ground color:

Me.Section(0).BackColor = Me.ColorCode
 
You can't use conditional formatting since that limits you to four choices -
3 plus the default. You will need to use code.

Select Case Month(Me.yourdatadate)
case 1
Me.somefield.ForeColor = 13170685 (light yellow)
case 2
...
End Select

Another option would be to create an array to hold the color values and then
use the month as an index to retrieve the value.

You can also use the Choose() function since the list is limited.

The easiest way to get the color values is to use the color picker for a
field and then copy the generated number.
 
I would create a table of month numbers with the color value needed.
tblMthColors
=======================
MthNum MthColor
1 255
2 16711680
3 26367

You could add this table to your report's record source and set the criteria
like:
WHERE Month([DateField]) = [MthNum]

This will allow you to add the MthColor to a hidden bound text box on your
report. Use code in the report section's On Format event to set the ForeColor
of your controls to the MthColor.
 
Thanks Allen, that looks like it might do the trick though I did wonder if
there was a much simpler solution using Conditional Formatting.

I can set the colours of months using the following CF Expression

DatePart("m", [Order Date]) = 1

But can only seem to apply 3 conditions when obviously I need 12. I will
give your solution a go but can you see of any way of using the above as it's
a much less convoluted answer.
 
That's right: CF won't work, as it is limited to 3 conditions, and there are
more than 3 months in the year.

Looks like Marshall and Duane gave you the same solution. Pat's works in the
same way: less flexible to maintain, but simpler to implement.
 
I thought about that too and would probably make a table if doing it for
myself. However, in this case, I didn't think there would be much chance of
a new month being added any time soon so I went with the quick and dirty.
<g>

Allen Browne said:
That's right: CF won't work, as it is limited to 3 conditions, and there
are more than 3 months in the year.

Looks like Marshall and Duane gave you the same solution. Pat's works in
the same way: less flexible to maintain, but simpler to implement.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Richard Horne said:
Thanks Allen, that looks like it might do the trick though I did wonder
if
there was a much simpler solution using Conditional Formatting.

I can set the colours of months using the following CF Expression

DatePart("m", [Order Date]) = 1

But can only seem to apply 3 conditions when obviously I need 12. I will
give your solution a go but can you see of any way of using the above as
it's
a much less convoluted answer.
Click to expand...
Click to expand...
 
Back
Top