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

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?
0
Utf
1/10/2008 2:54:01 PM
access.reports 4434 articles. 0 followers. Follow

7 Replies
572 Views

Similar Articles

[PageSpeed] 54

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

-- 
Allen Browne - Microsoft MVP.  Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Richard Horne" <RichardHorne@discussions.microsoft.com> wrote in message
news:25C170C1-C862-4AAB-B209-BE191B299F11@microsoft.com...
>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? 

0
Allen
1/10/2008 3:31:35 PM
Richard Horne wrote:

>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.


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

-- 
Marsh
MVP [MS Access]
0
Marshall
1/10/2008 3:37:45 PM
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.

"Richard Horne" <RichardHorne@discussions.microsoft.com> wrote in message 
news:25C170C1-C862-4AAB-B209-BE191B299F11@microsoft.com...
>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? 


0
Pat
1/10/2008 3:39:36 PM
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.
-- 
Duane Hookom
Microsoft Access MVP
If I have helped you, please help me by donating to UCP 
http://www.access.hookom.net/UCP/Default.htm


"Richard Horne" wrote:

> 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?
0
Utf
1/10/2008 3:41:02 PM
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.


0
Utf
1/10/2008 3:42:05 PM
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
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Richard Horne" <RichardHorne@discussions.microsoft.com> wrote in message
news:28A321C2-4CBE-495F-82D4-CB3B87D68F3B@microsoft.com...
> 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. 

0
Allen
1/11/2008 1:23:34 AM
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" <AllenBrowne@SeeSig.Invalid> wrote in message 
news:%23UT4WC$UIHA.4752@TK2MSFTNGP05.phx.gbl...
> 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
> Tips for Access users - http://allenbrowne.com/tips.html
> Reply to group, rather than allenbrowne at mvps dot org.
>
> "Richard Horne" <RichardHorne@discussions.microsoft.com> wrote in message
> news:28A321C2-4CBE-495F-82D4-CB3B87D68F3B@microsoft.com...
>> 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.
> 


0
Pat
1/11/2008 9:50:26 PM
Reply:

Similar Artilces:

How to make return address labels in Publisher?
Can you do the title above in Publisher? Sure. I've done them in different label sizes. What size labels are you interested in? -- Don Vancouver, USA "Robert" <Robert@discussions.microsoft.com> wrote in message news:23DBCD56-027A-406F-B9D1-3EF45B8E33C0@microsoft.com... > Can you do the title above in Publisher? Thank you. I use the 2.5" x 1" size. it is a standard Avery Label size. "Don Schmidt" wrote: > Sure. I've done them in different label sizes. What size labels are you > interested in? > > > -- > Don > Van...

Help with conditional formatting with 2000
Any help would be greatly appreciated. I am trying to group data together into increments of 10% of th numbers and then chart them based on these groups. For example, I hav 300 data points that vary from 20 to 500 in value. I want them t appear in a chart based on the number of values that fall in the lowes 10% of numbers (ie. 20-40) then the next 10% (ie. 40-60) etc. up to th top 10% of numbers, but I do not want to manually determine what thes ranges are. I want to see a distribution of how many numbers fal within each 10% of values. I am not sure if this makes sense, please let me know...

Date Formatting when Concantenating
I have a simple question. I have a cell that has date that looks lik this: 10/15/1999 14:34 When I use the concantenate feature my date looks like this: 36448.6073611111 I tried to format the call every which way - but I cannot get it t look the original. Feeling really silly for even asking.. thanks all for your help -- Message posted from http://www.ExcelForum.com Hi bleu808! Use: ="Today is "&TEXT(TODAY(),"mm/dd/yyyy hh:mm") -- Regards Norman Harker MVP (Excel) Sydney, Australia njharker@optusnet.com.au "bleu808 >" <<bleu808.189yij...

1 Chart
I presently have an XY line chart showing asset price over time. Pretty simple. X Axis - Time Scale Y Axis - Asset Price I would now like to add an additoinal series showing the volume of assets traded, ideally this would be as a bar chart sitting "underneath" the asset price on the chart. They would share the same X Axis. I have added another series, but this simply displays the volume traded as another line, and even when this is set to a secondary axis the scaling makes this unworkable. i have adjusted the scales of both, still this does not make it workable, i want the series...

Report Format
Env: CRM 3, VS 2003, remote connect to CRM server. I've created a QUOTE in VS2003 and when viewed using PREVIEW from within the VS environment it produces a beautiful QUOTE (at least in my opinion). When printed, again from the VS environment, it is perfect. When I upload the RDL file to the CRM server, create an new REPORT using this RDL then produce a REPORT, the formatting is all over the place. When I inspect the source (in VS) there are no fields that extend past the defined size. It appers that the CRM report engine randomly adds CRLFs all thru the report ... ??? ...

How can I clear the last Data->Text to columns to formatting
I've noticed in Excel 2000 that if I paste text into various worksheets within a workbook each paste will assume the Text->Column formatting that I applied in the previous. How can I prevent it from happening ? Thanks Steve Just run another data|Text to columns against a dummy cell. Specify delimited, but remove all the check marks from all the possible delimiters. (alternatively, you can close excel and reopen it.) svaardt wrote: > > I've noticed in Excel 2000 that if I paste text into various worksheets > within a workbook each paste will assume the Text->Col...

Percentage difference calc that knows the largest figure
I have a calculation in cell A3 which looks at the content of Cell A1 and Cell A2 and then works out the difference between both as a percentage For instance Cell A1 = 100 Cell A2 = 10 Cell A3 returns the difference as being 90% My simple calculation in Cell A3 is as follows =(A1-A2)/A1 Cell A3 is formatted to give the answer as a percentage to 2 decimal places The above works fine as long as the number in Cell A1 is greater than the number in Cell A2 If the number in A2 is greater than the number in A1 then my calculated answer is incorrect For instance Cell A1 = 10...

Conferting File format
One of my users stored 250 photos on CD in a pdf format. The person that will work with them wants them in a raw format. Is there any way to convert the entire disk at one time to a raw format, rather than opening each file and saving in the new format? Thank you. vsp deborah <vspdeborah@discussions.microsoft.com> was very recently heard to utter: > One of my users stored 250 photos on CD in a pdf format. The person > that will work with them wants them in a raw format. Is there any way > to convert the entire disk at one time to a raw format, rather than > opening each f...

Too many different cell formats #6
I am running into the error message: Too many different cell formats Is there a solution to lowering the number of formats I am using? Just trying to change them to make some consistent gives me the same error message. I tried running the search on the forums on my topic but they have been disabled for a Microsoft upgrade. Thanks! One idea - Rob Bovey's excellent Utilities add-in will list all the formats in use in your workbook, allowing you to manually delete what isn't being used. http://www.appspro.com/Utilities/ExcelUtilities.htm You can also see the source code for ...

How to get only the year in the date format in Access
How to get only the year in the date format I.e in the table in need to display only year E.g 2005 - should be display " 05" automatically Custom format the cell as: yy -- HTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "yanu" <yanu@discussions.microsoft.com> wrote in message news:14CE9F60-F7B9-467A-8C16-71088C31BEBA@microsoft.com... > How to get only the year in the date form...

date/time formatting
I download a csv report from a web based program. Everything works great but the date comes through as "Jul 21 2009 1:51pm". I do not need the time in my report. I have tried reformatting the cells, tried to copy to a new file with the cells already formatted to "7/21/09", opening the cell format using the F2 key and deleting the time (time will still not be formatted correctly). The only way I get get just the time is to retype every cell. What am I doing wrong? I am using Excel 2002 SP3 TIA, Cindy When data is downloaded from the web, a lot of "other&quo...

format cell #4
In Access, I can set up a field that "forces" the user to enter info - a date, for example - in a certain way, such as 25 Jan 05 or enter time as 12:15 AM. Is there a way that I can "force" this in excel? Thank you. Hello- Without invoking something more technical, you can select the cell(s) and go to Data>Validation and choose what type of entry be allowed in the field. Format the cell in the manner you wish to have the date or time expressed. HTH |:>) "HJC" wrote: > In Access, I can set up a field that "forces" the user to enter in...

unicode format files for Outlook 2002
I use Outlook 2002. I would like to use Unicode files (for larger size). I see references to using it with Outlook 2003, but none for 2002. can unicode files be used with Outlook 2002? If so, how? thanks, Huck No OL 2003 & 2007 only "Huck Rorick" <huckrorick@groundwork.org> wrote in message news:epYUYAl4IHA.2348@TK2MSFTNGP06.phx.gbl... >I use Outlook 2002. I would like to use Unicode files (for larger size). >I see references to using it with Outlook 2003, but none for 2002. can >unicode files be used with Outlook 2002? If so, how? > > than...

Formatting cells and getting pound signs
I am using Excel 2003 with all updates as of 4/28/04 and trying to format a cell using the custom category and choosing the #,##0.00 type. I am trying to add the $ symbol at the beginning of the type and add text at the end of the type to look like this $#,##0.00 "text". When I do this however it shows up in my cell on my worksheet as ##########. It does know what the value is and shows as I would expect it to when I place mouse over cell in a balloon If I use only the $ symbol befor the type it shows fine. If I use only the "text" after the type is shows fine. Using the...

Does anyone have a format or template for a Education Cover Letter
Does anyone have a format or template for a Education Cover Letter? To Whom It May Concern: Enclosed please find an education. Sincerely, eb -- Suzanne S. Barnhill Microsoft MVP (Word) Words into Type Fairhope, Alabama USA http://word.mvps.org "eb" <eb@discussions.microsoft.com> wrote in message news:4CEF7B84-6F7A-4D07-97B3-F349F3B11E54@microsoft.com... > Does anyone have a format or template for a Education Cover Letter? > "Suzanne S. Barnhill" <sbarnhill@mvps.org> wrote in message news:O3aNFsq2KHA.5212@TK2MSFTNGP04.phx.g...

Selecting cell value for a sum, based on a condition
Trying to come up with a formula or method that will enable me to sum values based on a condition. For example, I have three columns which contain a condition and two amounts. If the condition is of the 'each' variety, one value will be used in the sum. If the condition is of the "square foot" variety, another value will be used. Here is a small diagram that may help visualize this: A B C D 1 Measure Unit Cost S.F. Cost Summed Total 2 Each 3.00 .30 3 S.F....

Conditional Formatting on cells beginning with a hyphen
Is it possible to do conditional formatting on cells beginning with a hyphen? Thanks, Greg 1. Place the cursor in A1 cell and select the Range 2. From menu Format>Conditional Formatting> 3. For Condition1>Select 'Formula Is' and paste the below formula =LEFT(A1,1)="-" 4. Click Format Button>Font>Color select your desired font & Background Color pattern and then give ok Change the cell reference of A1 to your desired cell, if required. But keep in mind that when applying the conditional formatting the Active cell should be in the ce...

time formats #3
I have an Excel sheet with a long list of times spent on various projects. The times should all be in minutes and seconds. The first time reads 2:29 and I know that is accurate, for 2 min, 29 seconds. In the formula bar, it reads, 2:29:00 AM. Another...the cell displays 0:40 and I know that is right, for 40 seconds. But in the formula bar, I see 12:40:00 AM I want numbers, but apparently I am looking at times. I tried changing the format. When I check the format for 2:29, it comes up Custom and says it is hh:mm, not mm:ss. I tried changing it to mm:ss, but that changes the displa...

an attempt was made to load a program with an incorrect format
Hello, This is the error I get on Windows 7. I tried going to the project screen in vb.net Studio 2008, and changing the target cpu to x64. This did not work. Do I have to build the program on Windows 7? I'm sure this must be a common problem for programs going to Windows 7. Thanks for any help. Scott On May 22, 2:17=A0am, "Scott Baxter" <sc...@websearchstore.com> wrote: > Hello, > > This is the error I get on Windows 7. > > I tried going to the project screen in vb.net Studio 2008, and changing t= he > target cpu to x64....

Error trying to make an account default
I have a new computer and my outlook 2003 is not letting me assign a default account to send and receive. I get this error: "The Specicied Account could not be found. It might have been deleted." I have four accounnts setup and have tried removing the accounts and recreating them and still get the error. when the account has been made, it tests fine. None of the four can be made default. Thanks in Advance! ...

Excel Number Format Codes
I can't figure out how to format numbers so that when you type 3220 it will look like 32.20 kinda like how you can enter numbers on an adding machine. I would greatly appreciate this number format code. Thank you. -- 1:~ Hi, I think you want to go Tools | Options | Edit | Fixed Decimals - 2. That will divide all the numbers you enter by 100. You will also have to format the row or column to display 2 decimals. Select the cells or range, right mouse click, format cells, numbers, 2 decimals. Hope that helps. Best regards, Kevin "MBB" <MBB@discussions.microsoft.com> w...

EXTRACTING UNIQUE RECORD BASED ON CONDITION
This is a multi-part message in MIME format. ------=_NextPart_000_0012_01C781BF.08FB92F0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable Hello everyone! I would like to extract unique records based on a condition. For = example, how to extract unique record from column 'B' when column 'A' = has "AP" or any other desired condition. The data is as follows: A B =20 MI 70056542=20 MI 70056543=20 AP PATRICK CUDAHY INCORPORATED=20 AP PATRICK CUDAHY INCORPORATED=20 AP SUGAR CREEK ...

Users cannot login to OWA after Exchange 2K3 was moved into different OU
I created a new OU named member servers, and moved the exchange 2k3 server into it. After I moved it, only the administrator was able to login to OWA successfully. No users could login to OWA. I created a test account with a mailbox, and was able to login to OWA with the newly created test account. I then moved the exchange 2k3 server from the member servers OU into the computers OU, and the newly created account was unable to login to OWA. Any help with this would be appreciated. ...

Save custom formats independantly of workbooks?
Hi, Is there a way to save several custom formats (say, numbers in $000's, in $ millions, etc) which I would use for a variety of workbooks (including workbooks where those formats do not exist yet), with a name, and make them accessible from the toolbars? Earlier today someone kindly suggested to assign a name to the format using Format -> Style, but the formats I had saved have disappeared from the style menu once I closed a spreadhseet and used a different one. Any suggestion? Many thanks, Joss Delage ...

Moving certain data to different sheet
I need to move data that meets a certain criteria, to another sheet within a workbook. For instance, if a column of data is for a certain ZIP code area, I need it to automatically copy to a sheet for that city. Say, 40202 would go to the Louisville, KY sheet. Because Louisville has multiple ZIPs, I would need only the data that begins with 402 to go to that sheet. Lexington KY's data, which begins with ZIP code 405, would go to its own sheet. Macro? Formula? Thanks! This can definitely not be created with a formula. I suggest that you make use of the macros. Rgrds, Kris...