title: Using DateDiff in Access to colour code dates in Conditional Formatting
body: <p>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.</p>
  
  <p>I have got the text to default as black and then have </p>
  
  <p>RED</p>
  
  <pre><code>[C_ExpiryDate]&lt;Now() 
  </code></pre>
  
  <p>AMBER</p>
  
  <pre><code>DateDiff("m",[C_ExpiryDate],Now())&lt;6 And [C_ExpiryDate]&gt;Now()
  </code></pre>
  
  <p>GREEN</p>
  
  <pre><code>DateDiff("m",[C_ExpiryDate],Now())&gt;6 And [C_ExpiryDate]&gt;Now()
  </code></pre>
  
  <p>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.</p>
  
  <p>Not sure if it might be easier to default as green and perhaps write something like</p>
  
  <pre><code>[C_ExpiryDate] = ""
  </code></pre>
  
  <p>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.</p>
  
  <p>Any suggestions appreciated...</p>