Conditional Formatting in Access wwith Dates

Question

I'm having an issue with conditional formatting in Access where I need to format a value if the date occured before today and another field is blank. It works fine for most values, but for some reason when the day value is in the single digits of the current month, or you go two months ahead the value isn't read properly.

The Date on the left is referred to as [Plan_Date] and the column on the right is referred to as [Act_Date]. The premise is that if [Act_Date] is blank and [Plan_Date] occurs before today then the field should be highlighted. I'm using the expression:

[Act_Date]="" And [Plan_Date]<Date() in my conditional formatting.

Try playing around with some of these: http://stackoverflow.com/questions/3106624/now-function-with-time-trim — Barry, Aug 25 '14 at 13:57

score 2 · Accepted Answer · answered Aug 25 '14 at 13:59

The problem is that [Plan_Date] is being treated as a string and forcing an alphabetical order comparison between [Plan_Date] and Date(). The following strings are sorted in ascending alphabetical order:

11/15/2014
8/12/2014
8/25/2014
8/3/2014

You need to force [Plan_Date] to a date type for comparison purposes:

[Act_Date] = "" And CDate([Plan_Date]) < Date()

Conditional Formatting in Access wwith Dates

1 Answers1