Conditional Formatting with Dates

[Guest]

I want to put conditional formatting on a field on my form so that if the
date is less than today the field is red and if the date is between today and
three months from today then it is yellow....otherwise it is just the default.
I was using conditional formatting and setting expressions. The red
condition works where if the date if less than today it appears red. The
problem is with the yellow (if it is between now and three months from
now)...it will do a whole year.
I've tried a lot of different code...as of now this is what I have:

Red......(date is before todays date)
Expression Is:
WED.Value < (Format(Date(),"yyyy/mm/dd"))

Yellow....(btwn today and three months form today)
Expression Is:
WED.Value > (Format(Date(),"yyyy/mm/dd")) And WED.Value <
(DateAdd("m",3,Format(Date(),"yyyy/mm/dd")))

WED being our textbox.

Any ideas would be appreciated.....thanks

 

[Jeff O via AccessMonster.com]

Try using the dateadd function. I use it for date compares and it works
great.

/Jeff

 

[Jeff O via AccessMonster.com]

Sorry about that. I jumped the gun and didn't see you were already using
dateadd.

/Jeff

 

[Jeff O via AccessMonster.com]

You might try converting the today and 3 months from now dates to a julian
date and testing between numerics. It may work better for this app.

/Jeff

 

[Guest]

We were trying to do this within the conditional formatting option in Access
and not through code....
If we were going to do it within code we don't know where to put is as we
need the color of the testbox to change while we cycle through all the
records and that wasn't working.

Turns out the DateAdd fuction isn't recognizing the formatted date so then
it is comparing two different formats so it doesn't work.

The date format we are using is yyyy/mm/dd