Conditional Formatting

  • Thread starter Thread starter news.microsoft.com
  • Start date Start date
N

news.microsoft.com

Access 2007

I have a report that has various Due Dates

I would like 2 things to happen:

1) When the calculated date is before todays date to print in Red

2) When the calculated date is within 1 month of today's date to print in
Orange.

eg
Today's date is 2nd Feb 2009

Calculated date = 3rd Jan 2009 this would be Red
Calculated date = 23rd Feb 2009 - this woud be Orange
Calculated date = 3rd Mar 2009 - this would be black

Ive tried conditional formatting but can't seem to get it to work.

Thanks

A
 
First, amke sure that your calculated date is actually in the query - that
helps immensely, I believe.

Set your conditional formatting options.

Set you conditional formating drop down to "Expression is", and enter a
value into the box next to "expression is" that will evaulate to TRUE or
false. When the expression is TRUE, the conditional formatting will be
applied.

I would recommend something like

[Calculatedvalue]<=DateAdd("m",-1, Date())

for the month calculation. You may need to do some fidgeting to account for
time of day.

HTH
blitz
 
Thanks for your help but it still doesn't seem to work.

I have the following dates in the report that are calculated in the query
using the DateAdd function. The field name is [RetDue]

25/01/2007
10/10/2008
29/08/2008
20/02/2009
22/04/2009

In the Conditional Formatting for the RetDue field I have:
Expression Is [RetDue]>Date() And [RetDue]<=DateAdd("m",1,Date())
Format: Text Colour = Orange

Expression Is [RetDue]<=Date()
Format: Text Colour = Red

If todays date is 3rd Feb (03/02/2009) I would expect the following results:

25/01/2007 - Red Text
10/10/2008 - Red Text
29/08/2008 - Red Text
20/02/2009 - Orange Text
22/04/2009 - Black Text

What I get is that it doesn't format any of the dates - leaves them all as
the default black

What am I doing wrong here?

Thanks

Yblitzka said:
First, amke sure that your calculated date is actually in the query - that
helps immensely, I believe.

Set your conditional formatting options.

Set you conditional formating drop down to "Expression is", and enter a
value into the box next to "expression is" that will evaulate to TRUE or
false. When the expression is TRUE, the conditional formatting will be
applied.

I would recommend something like

[Calculatedvalue]<=DateAdd("m",-1, Date())

for the month calculation. You may need to do some fidgeting to account
for
time of day.

HTH
blitz

news.microsoft.com said:
Access 2007

I have a report that has various Due Dates

I would like 2 things to happen:

1) When the calculated date is before todays date to print in Red

2) When the calculated date is within 1 month of today's date to print in
Orange.

eg
Today's date is 2nd Feb 2009

Calculated date = 3rd Jan 2009 this would be Red
Calculated date = 23rd Feb 2009 - this woud be Orange
Calculated date = 3rd Mar 2009 - this would be black

Ive tried conditional formatting but can't seem to get it to work.

Thanks

A
Click to expand...
Click to expand...
 
Figured it out

I needed to add CDate to the expression like so
CDate([RetDue])>Date() And CDate([RetDue])<=DateAdd("m",1,Date())

news.microsoft.com said:
Thanks for your help but it still doesn't seem to work.

I have the following dates in the report that are calculated in the query
using the DateAdd function. The field name is [RetDue]

25/01/2007
10/10/2008
29/08/2008
20/02/2009
22/04/2009

In the Conditional Formatting for the RetDue field I have:
Expression Is [RetDue]>Date() And [RetDue]<=DateAdd("m",1,Date())
Format: Text Colour = Orange

Expression Is [RetDue]<=Date()
Format: Text Colour = Red

If todays date is 3rd Feb (03/02/2009) I would expect the following
results:

25/01/2007 - Red Text
10/10/2008 - Red Text
29/08/2008 - Red Text
20/02/2009 - Orange Text
22/04/2009 - Black Text

What I get is that it doesn't format any of the dates - leaves them all as
the default black

What am I doing wrong here?

Thanks

Yblitzka said:
First, amke sure that your calculated date is actually in the query -
that
helps immensely, I believe.

Set your conditional formatting options.

Set you conditional formating drop down to "Expression is", and enter a
value into the box next to "expression is" that will evaulate to TRUE or
false. When the expression is TRUE, the conditional formatting will be
applied.

I would recommend something like

[Calculatedvalue]<=DateAdd("m",-1, Date())

for the month calculation. You may need to do some fidgeting to account
for
time of day.

HTH
blitz

news.microsoft.com said:
Access 2007

I have a report that has various Due Dates

I would like 2 things to happen:

1) When the calculated date is before todays date to print in Red

2) When the calculated date is within 1 month of today's date to print
in
Orange.

eg
Today's date is 2nd Feb 2009

Calculated date = 3rd Jan 2009 this would be Red
Calculated date = 23rd Feb 2009 - this woud be Orange
Calculated date = 3rd Mar 2009 - this would be black

Ive tried conditional formatting but can't seem to get it to work.

Thanks

A
Click to expand...
Click to expand...
Click to expand...
 
Back
Top