Shading a cell in a report

[Guest]

I have created a report where I need to add shading to a field. This field
should auto-shade if a date is within a certain range. There are twelve
cells (one for each month).

For example : if Authorization Date is bewteen Jan 1 2005 and Jan 31 2005
then shade grey, if not it would remain normal.

Can this be written as an event code specific to only that field?

Thanks for any help you can provide.

 

[Roger Carlson]

In Design View of your report, click on the textbox in question, then on the
Menu go to Format > Conditional Formatting... You can add you condition and
shading there.

--
--Roger Carlson
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

 

[Guest]

On the Format property of the section where the field is, you can write the
code

If Me.[Authorization Date]>= #1/1/2005# and Me.[Authorization Date] <=
#31/1/2005# then
me.[Authorization Date].BackColor = 12632256 'Gray
Else
me.[Authorization Date].BackColor = 16777215 'White
End If

 

[Guest]

How would I make that specific to the January field and then repeat it for
the February, March, etc? I need them each to shade idependant of eachother
- based on the Auth Date.
--
~April

On the Format property of the section where the field is, you can write the
code

If Me.[Authorization Date]>= #1/1/2005# and Me.[Authorization Date] <=
#31/1/2005# then
me.[Authorization Date].BackColor = 12632256 'Gray
Else
me.[Authorization Date].BackColor = 16777215 'White
End If
-------------------------------------------------------------
Or
You can check about conditional formatting, incase that filter is fixed.

I have created a report where I need to add shading to a field. This field
should auto-shade if a date is within a certain range. There are twelve
cells (one for each month).

For example : if Authorization Date is bewteen Jan 1 2005 and Jan 31 2005
then shade grey, if not it would remain normal.

Can this be written as an event code specific to only that field?

Thanks for any help you can provide.

 

[Guest]

I'm still not having any luck. How would I write an expression in
"Conditional Formatting) to tie the field to the Auth Date? The field itself
(the one I want shaded) sums up dollars from other fields in the report and
does not equal a date. I just need it shaded if that month (Jan_Actuals) is
the month dollars are authorized.

 

[Guest]

How do you get the month you are filtering on?
If you want to gray the field only if it equal to the current month, then

If month(Me.[Authorization Date]) = month(date()) then
me.[Authorization Date].BackColor = 12632256 'Gray
Else
me.[Authorization Date].BackColor = 16777215 'White
End If
Or you can use

If month(Me.[Authorization Date]) = 1 then ' Jan
me.[Authorization Date].BackColor = 12632256 'Gray
Else
me.[Authorization Date].BackColor = 16777215 'White
End If

========================================
I feel that I don't fully understand what you are trying to do.

How would I make that specific to the January field and then repeat it for
the February, March, etc? I need them each to shade idependant of eachother
- based on the Auth Date.
--
~April

On the Format property of the section where the field is, you can write the
code

If Me.[Authorization Date]>= #1/1/2005# and Me.[Authorization Date] <=
#31/1/2005# then
me.[Authorization Date].BackColor = 12632256 'Gray
Else
me.[Authorization Date].BackColor = 16777215 'White
End If
-------------------------------------------------------------
Or
You can check about conditional formatting, incase that filter is fixed.

I have created a report where I need to add shading to a field. This field
should auto-shade if a date is within a certain range. There are twelve
cells (one for each month).

For example : if Authorization Date is bewteen Jan 1 2005 and Jan 31 2005
then shade grey, if not it would remain normal.

Can this be written as an event code specific to only that field?

Thanks for any help you can provide.

 

[Roger Carlson]

In the dropdown where is says Field Value Is, click the down arrow and
choose Expression Is... In the expression box, put

[Authorization Date] Between #1/1/2005# and #1/31/2005

then select the formatting.

 

[Guest]

The Authorization date is a hard date, entered by the user.

Here's the scenario. My company is required to submit applications to our
states Public Service Commission. They must authorize our projects before
construction begins. The spending before construction (authorization) is
called Precert, after we recieve the authorization to build the project, the
spending is classed as capital. When we budget we lump our spending into
these two categories.

After an application is submitted we anticipate that will take anywhere from
5 months to 18 months for approval (depending on the project). I manually
add this date to my table (Project Info), it does not auto-calculate.

My report recaps different project details which includes estimated spending
while in "precert". I want to use the shading as a way to designate the
month when precert ends.

I have twelve columns that sum by month in my "year" footer. I need each of
these fields to tie to the PSC_Approval date individually and shade if the
date falls within that fields month. So if my approval date is 3/15/2005
only March will shade.

That might be more information than you need, but hopefully it helps you to
understand the situation a little bit better.

Thanks again for your help.



--
~April

How do you get the month you are filtering on?
If you want to gray the field only if it equal to the current month, then

If month(Me.[Authorization Date]) = month(date()) then
me.[Authorization Date].BackColor = 12632256 'Gray
Else
me.[Authorization Date].BackColor = 16777215 'White
End If
Or you can use

If month(Me.[Authorization Date]) = 1 then ' Jan
me.[Authorization Date].BackColor = 12632256 'Gray
Else
me.[Authorization Date].BackColor = 16777215 'White
End If

========================================
I feel that I don't fully understand what you are trying to do.

How would I make that specific to the January field and then repeat it for
the February, March, etc? I need them each to shade idependant of eachother
- based on the Auth Date.
--
~April

On the Format property of the section where the field is, you can write the
code

If Me.[Authorization Date]>= #1/1/2005# and Me.[Authorization Date] <=
#31/1/2005# then
me.[Authorization Date].BackColor = 12632256 'Gray
Else
me.[Authorization Date].BackColor = 16777215 'White
End If
-------------------------------------------------------------
Or
You can check about conditional formatting, incase that filter is fixed.


:

I have created a report where I need to add shading to a field. This field
should auto-shade if a date is within a certain range. There are twelve
cells (one for each month).

For example : if Authorization Date is bewteen Jan 1 2005 and Jan 31 2005
then shade grey, if not it would remain normal.

Can this be written as an event code specific to only that field?

Thanks for any help you can provide.

 

[Guest]

That almost worked! My data seperates by year so now all of the January
totals for 2005, 2006 and 2007 shade - My PSC_Approval date is 1/12/2006 so
only January 2006 should shade. Any other ideas?
--
~April

In the dropdown where is says Field Value Is, click the down arrow and
choose Expression Is... In the expression box, put

[Authorization Date] Between #1/1/2005# and #1/31/2005

then select the formatting.

 

[Guest]

I figured it out! But only with your help! Here is what I wrote:

[PSC_Approval] Between #01/01/2005# And #01/31/2005# And [YEAR]=2005

I did this for all three years for the january months (1/06 and 1/07) and it
worked. I change my date and the shading follows! Thanks so much for both
of your help


--
~April

In the dropdown where is says Field Value Is, click the down arrow and
choose Expression Is... In the expression box, put

[Authorization Date] Between #1/1/2005# and #1/31/2005

then select the formatting.