Does the conditional formatting dialogue still show incorrectly, or does it look the same as in the image I posted earlier? If the former, it suggests that there might be corruption of the conditional formatting mechanism. Using DateDiff in Access to colour code dates in Conditional Formatting. I have a table of clearances and their expiry dates. If a clearance has expired I would. Access' conditional formatting feature is just as comprehensive, flexible, and powerful as Excel's, but many users aren't aware it exists. Many Access users would like to highlight report. MS Excel 2010: Automatically highlight expired dates and dates that are 30 days from expiration This Excel tutorial explains how to use conditional formatting to automatically highlight expired dates and dates that are 30 days from expiration in Excel 2010 (with screenshots and step-by-step instructions). Access' conditional formatting feature is just as comprehensive, flexible, and powerful as Excel's, but many users aren't aware it exists. Many Access users would like to highlight report.
I have a table of clearances and their expiry dates. If a clearance has expired I would like it to be red, if it expires within 6 months I want it to be amber, if it expires in more than 6 months green and if it is yet to be granted (hence doesn't yet have an expiry date) then black.
This seems to have a few different problems, the red works fine from what I can tell. But the other two seem to be handled poorly, for example a clearance with 06 Sep 2015 is amber (today is 03 July 2014 so this should be green). Similar for 05 Mar 2017, I think it is just looking at the month regardless of the year, but I'm not too sure how to handle this better.
Not sure if it might be easier to default as green and perhaps write something like
But this didn't seem to do anything and everything was coming up as green even when there was a blank date. Similar for single quotations.
Not the answer you're looking for? Browse other questions tagged vbams-accessaccess-vbadatediffconditional-formatting or ask your own question.
Microsoft Access
Use conditional formatting in Access forms and reports
Many Access users would like to highlight report information, conditionally; similar to the way Excel users employ conditional formatting. For instance, the simple report shown below displays a due date in red when it falls within the current week. It's an easy way to visually alert users that a project enters its final week.
Fortunately, this report solution doesn't require a complex expression or even VBA - your users can use a conditional formatting rule. If your users are familiar with Excel, they'll need little training because the feature works similarly in both applications. Before we work through the example, you should know that I based this example on the Events template that comes with Access 2010. You can work with most any report, as long as there's a date field to work with. I entered just three records to keep things simple. To apply this particular conditional formatting rule to a date field in a report (the All Tasks report), do the following:
Open the report in Design view.
Select the date field. In this case, that's the Due Date field.
Click the contextual Format tab.
Click Conditional Formatting in the Control Formatting group.
In the resulting dialog, click New Rule.
The Select A Rule Type option will default to the right option, Check Values In The Current Record Or Use An Expression, so don't change that option.
Now you're ready to define the rule. Field Value Is is the appropriate option in the first dropdown control, so don't change it.
Choose Less Than Or Equal To from the second dropdown.
Enter the following expression: Now()+7. (That period is grammatical and not part of the expression.)
From the Font Color dropdown, choose Red.
Click OK. Your report should resemble the one shown above in Report View.
In English, this rule says the following: If the due date falls within the next seven days, display that date in red.
In this example, we applied only one simple rule, but the feature is much more robust. Access' conditional formatting feature is just as comprehensive, flexible, and powerful as Excel's. Use it to alert users conditionally, in both forms and reports.