Conditional formatting in Access 2010 report (1 Viewer)

Good Morning all

I have a quick question about formatting a filed in an access 2010 report?

Firstly I thought there was a limit of 3 rules on conditional formatting I tried to add additional rules and there seems to be no problem doing that.

And they all fire with varying degrees of success the one I seem to be struggling with at the moment is the following

IN ENGLISH

If the frequency is 3 and todays date is between 56 and 121 days From the stored date turn the cell green

So I have in the expression as follows

[Frequency]=3 And (Date()>DateAdd("d",56,[Date]) And Date()<DateAdd("d",121,[Date]))

I also have a second expression that is supposed to be

Turn cell red if Frequency = 3 and when today is greater then 4 Months (121days) from the Stored date. that expression is

[Frequency]=3 And (Date()>DateAdd("d",121,[Date]))

The first expression fires Ok but when I change [Date] to >121 days and then back to between 56 and 121 days the formatting doesn't change as I'd expect it changes between red and green at about 90 days from stored instead ?

Any Ideas?

Is it because I have more then 3 rules in the conditional formatting or are my conditions a little iffy?.

Many thanks

Zippy483

couple of things - your field is called Date which is a reserved word and may be confusing the system - try changing it to something more meaningful

Also you have
(Date()<DateAdd("d",121,[Date]))
and
(Date()>DateAdd("d",121,[Date]))

so neither will fire if Date=DateAdd("d",121,[Date])

Thanks for the Reply CJ

The Date field is something I'm going to have to live with I inherited the database and it's used all over the place

I also understand the 121 day problem I can fix that fairly easily I think, however the cross over between green and red seems to be at 96 days for some reason

I can't see anything wrong with your conditions - I still think your field name is likely to be the issue.

suggest for testing purposes, take a copy of your report, alias the date as 'testdate' or similar in the recordsource and test it

Right Made another field in the relevant query for the report and Mapped [DATE] to it as LastDone: [Date]

this essentially compies the date field to the LastDone field I then pointed the DATE control on the report to LastDone and change the conditional formatting to reflect this sadly it still works as before?

Plus point I now know the DATE wasn't compromising the condition

did you rename the control as well?

If you have a control called Date you are also asking for weird faults.
Call it txtDate so you know exactly what everything is referring to.
Also I assume the LastDone: [Date] field is actually a datetime data type ?

and did you remove date from your query - i.e. only leaving the LastDone column

My apologies for the tardiness of the reply

right I changed the DATE field to LastDone but still had problems, I'd tried to recycle some of the old formatting but for what ever reason that didn't seem to work with the new

I solved the problem by using the same rule as described in my first post changing DATE for LastDone and the frequency number and then number of days add rules for

36, 24, 12, 6, 3 and 1 monthly intervals and now all seems well

Many thanks for your pointers