conditional formatting+dates

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

Guest

Hello, all.

I have a beautiful report set up that lets my sales manager know if an
account wasn't served on its scheduled day. On this report I have a field for
"last sale date". I would like this field to be yellow if that date is more
than a week old, and red if it is more than a month old. This field will
always be at least 1 day older than today. All my attempts have been
spectacular failures. I'd appreciate any help. Thanks!
 
Hello, all.

I have a beautiful report set up that lets my sales manager know if an
account wasn't served on its scheduled day. On this report I have a field for
"last sale date". I would like this field to be yellow if that date is more
than a week old, and red if it is more than a month old. This field will
always be at least 1 day older than today. All my attempts have been
spectacular failures. I'd appreciate any help. Thanks!
Click to expand...

Using Access 2000 or newer?
In design view, select the date field.
Click on Format + Conditional Formatting
Select Expression Is from the Condition1 drop-down box.
Write in the next box:
[DateFieldName] >= DateAdd("m",-1,Date()) and < Date()-6

Set the Color to Yellow

Click on Add.
Set Condition2 to Expression Is.
As the expression, write:
[DateFieldName] < DateDiff("m",-1,Date())

Set the Color to Red.
 
Gimpy,
What version of Access are you using?
I'll assume you're working in A2003, and mean Format/Conditional Formatting...

For 7 Day:
FieldValue | Between | Date()-7 | DateAdd("m",-1Date()) (Yellow)

For 1 Month:
FieldValue | Less Than | DateAdd("m",-1, Date()) (Red)
 
Fred- Thanks for the reply, I tried your solution but now I'm getting an
error message that the "expression contains invalid syntax". I'm using Access
2000 (report) and the field name is [last sale], if that helps. I do
appreciate your help.

fredg said:
Hello, all.

I have a beautiful report set up that lets my sales manager know if an
account wasn't served on its scheduled day. On this report I have a field for
"last sale date". I would like this field to be yellow if that date is more
than a week old, and red if it is more than a month old. This field will
always be at least 1 day older than today. All my attempts have been
spectacular failures. I'd appreciate any help. Thanks!
Click to expand...

Using Access 2000 or newer?
In design view, select the date field.
Click on Format + Conditional Formatting
Select Expression Is from the Condition1 drop-down box.
Write in the next box:
[DateFieldName] >= DateAdd("m",-1,Date()) and < Date()-6

Set the Color to Yellow

Click on Add.
Set Condition2 to Expression Is.
As the expression, write:
[DateFieldName] < DateDiff("m",-1,Date())

Set the Color to Red.
Click to expand...
 
Al-

Thanks for your reply. I'm using Access 2000. I tried your solution and ALL
the dates are red. I've got to be doing something wrong. (the field name is
[last sale] if that helps. I appreciate your assistance.
 
Fred- Thanks for the reply, I tried your solution but now I'm getting an
error message that the "expression contains invalid syntax". I'm using Access
2000 (report) and the field name is [last sale], if that helps. I do
appreciate your help.

fredg said:
Hello, all.

I have a beautiful report set up that lets my sales manager know if an
account wasn't served on its scheduled day. On this report I have a field for
"last sale date". I would like this field to be yellow if that date is more
than a week old, and red if it is more than a month old. This field will
always be at least 1 day older than today. All my attempts have been
spectacular failures. I'd appreciate any help. Thanks!
Click to expand...

Using Access 2000 or newer?
In design view, select the date field.
Click on Format + Conditional Formatting
Select Expression Is from the Condition1 drop-down box.
Write in the next box:
[DateFieldName] >= DateAdd("m",-1,Date()) and < Date()-6

Set the Color to Yellow

Click on Add.
Set Condition2 to Expression Is.
As the expression, write:
[DateFieldName] < DateDiff("m",-1,Date())

Set the Color to Red.
Click to expand...
Click to expand...

Sorry, I miss-wrote the expression.
I have no idea what I was thinking of.

Expression1 Yellow
[last sale] >= DateAdd("m",-1,Date()) and [last sale] < Date()-6

Expression2 Red
[last sale]<DateAdd("m",-1,Date())
 
Gimpy,
There was a typo in my code, (missing comma)... hope you caught it... it should have
caused a syntax error...
Should be... ...|DateAdd("m", -1, Date())

I tested my code in A2003, and it worked.
Today TestDate
10/9/06 10/9/06 (Grey = Normal)
10/1/06 (Yellow >-7 days)
9/1/06 (Red > -1 month)

Still no luck? Copy "exactly" your CF statements, as I did in my previous post.
--
hth
Al Campagna
Candia Computer Consulting - Candia NH
http://home.comcast.net/~cccsolutions

There are issues with 2000 Conditional Formatting, but AFAIK it involves continuous
subform calcualated fields. Don't that applies here...
Gimpy815 said:
Al-

Thanks for your reply. I'm using Access 2000. I tried your solution and ALL
the dates are red. I've got to be doing something wrong. (the field name is
[last sale] if that helps. I appreciate your assistance.

Al Campagna said:
Gimpy,
What version of Access are you using?
I'll assume you're working in A2003, and mean Format/Conditional Formatting...

For 7 Day:
FieldValue | Between | Date()-7 | DateAdd("m",-1Date()) (Yellow)

For 1 Month:
FieldValue | Less Than | DateAdd("m",-1, Date()) (Red)
Click to expand...
Click to expand...
 
Fred-

Worked like a charm! Thank you so much!

fredg said:
Fred- Thanks for the reply, I tried your solution but now I'm getting an
error message that the "expression contains invalid syntax". I'm using Access
2000 (report) and the field name is [last sale], if that helps. I do
appreciate your help.

fredg said:
On Fri, 6 Oct 2006 13:06:01 -0700, Gimpy815 wrote:

Hello, all.

I have a beautiful report set up that lets my sales manager know if an
account wasn't served on its scheduled day. On this report I have a field for
"last sale date". I would like this field to be yellow if that date is more
than a week old, and red if it is more than a month old. This field will
always be at least 1 day older than today. All my attempts have been
spectacular failures. I'd appreciate any help. Thanks!

Using Access 2000 or newer?
In design view, select the date field.
Click on Format + Conditional Formatting
Select Expression Is from the Condition1 drop-down box.
Write in the next box:
[DateFieldName] >= DateAdd("m",-1,Date()) and < Date()-6

Set the Color to Yellow

Click on Add.
Set Condition2 to Expression Is.
As the expression, write:
[DateFieldName] < DateDiff("m",-1,Date())

Set the Color to Red.
Click to expand...
Click to expand...

Sorry, I miss-wrote the expression.
I have no idea what I was thinking of.

Expression1 Yellow
[last sale] >= DateAdd("m",-1,Date()) and [last sale] < Date()-6

Expression2 Red
[last sale]<DateAdd("m",-1,Date())
Click to expand...
 
Al-

That must have been it. Thanks for your help!

Al Campagna said:
Gimpy,
There was a typo in my code, (missing comma)... hope you caught it... it should have
caused a syntax error...
Should be... ...|DateAdd("m", -1, Date())

I tested my code in A2003, and it worked.
Today TestDate
10/9/06 10/9/06 (Grey = Normal)
10/1/06 (Yellow >-7 days)
9/1/06 (Red > -1 month)

Still no luck? Copy "exactly" your CF statements, as I did in my previous post.
--
hth
Al Campagna
Candia Computer Consulting - Candia NH
http://home.comcast.net/~cccsolutions

There are issues with 2000 Conditional Formatting, but AFAIK it involves continuous
subform calcualated fields. Don't that applies here...
Gimpy815 said:
Al-

Thanks for your reply. I'm using Access 2000. I tried your solution and ALL
the dates are red. I've got to be doing something wrong. (the field name is
[last sale] if that helps. I appreciate your assistance.

Al Campagna said:
Gimpy,
What version of Access are you using?
I'll assume you're working in A2003, and mean Format/Conditional Formatting...

For 7 Day:
FieldValue | Between | Date()-7 | DateAdd("m",-1Date()) (Yellow)

For 1 Month:
FieldValue | Less Than | DateAdd("m",-1, Date()) (Red)
--
hth
Al Campagna

Hello, all.

I have a beautiful report set up that lets my sales manager know if an
account wasn't served on its scheduled day. On this report I have a field for
"last sale date". I would like this field to be yellow if that date is more
than a week old, and red if it is more than a month old. This field will
always be at least 1 day older than today. All my attempts have been
spectacular failures. I'd appreciate any help. Thanks!
Click to expand...
Click to expand...
Click to expand...
 
Back
Top