Conditional Formatting with dates

[drewdrew]

Hi all,

I have trawled the sites to try and find an answer to a simple question with conditional formatting:



I am using Access 2013 with some dates populated in a ubound box. I would like conditional formatting applied to this box with the following thresholds:

Turn Red if the value is within 30 days of the ubound date
Turn Orange if the value is within 31 days and 60 days of the ubound date
Turn Green if the value is over 61 days of the ubound date

I have tried variants with trial and error and I cannot get a finalized result.

All help if appreciated.

[ssanfu]

Turn Red if the value is within 30 days of the ubound date

What do you mean "the value"?

You need two date controls. One is the unbound text box, what is the other date control??

Lets say you have a form bound to a table.
The table has a date field "NextReading".
The form is set to continuous forms view.
In the header is the unbound text box named "Text47".

In the details section is a text box control bound to the field "NextReading". The control is named "tbNextReading". (tb for text box)

To set up conditional formatting, you would click on the "tbNextReading" and open the conditional formatting dialog box.
Add a new rule
Field value BETWEEN [Text47]+31 and [Text47]+60
Then click on the bucket (background color) and select Orange.
Click on OK
Click on New Rule
Field value LESS THAN [Text47]+30
click on the bucket (background color) and select Red.
Click on OK
Click on New Rule
Field value GREATER THAN [Text47]+61
click on the bucket (background color) and select Green.
Click on OK
Click on OK

NOTE: the name of the text box in the form header MUST be enclosed in brackets, as in: [Text1]
NOTE 2: the format for the unbound text box in the header must be "Short Date". (Open the properties, click on the FORMAT tab\ FORMAT property)

Enter a date into the unbound text box Text47 in the header .
Change the date to see if the BG colors change.

[drewdrew]

Thanks for the reply. When I mentioned value, I was referring to the date value that gets pulled into the ubound box [qual_rsd_latest] from a table.



I have taken the above advice and implemented it... but all dates that are in the ubound box [qual_rsd_latest] display with an orange background... dates from 3 years ago or even2 days ago display orange.

[CJ_London]

you have not followed ssanfu's instructions

what you have added is the equivalent to 'if today is between today plus 31 days and today plus 50 days'

today can never be anything other than today - so can never be greater than today

you get the orange because 'today is less than today plus 30 days' is true