Conditional Formatting Dates

[Guest]

I am trying to use conditional formatting to colour code a last contact date
field (txtLastContact) as follows:
Date falls within the last 6 months - green
Date is between 6 months and a year ago - orange
Date is more than 1 year ago - red

My 3 'Expression Is' conditions are as follows:
1) [txtLastContact]>=DateAdd("m",-6,Date())
2) [txtLastContact]<DateAdd("m",-6,Date()) And
[txtLastContact]>DateAdd("y",-1,Date())
3) [txtLastContact]<=DateAdd("y",-1,Date())

Expression 1 appears to work, but if the date is more than 6 months old it
goes red (when I am expecting it to be orange). I had also tried 'Field Value
Is' and 'Between' for the 2nd condition, but to no avail.

Thanks

 

[Guest]

Changed the 2nd condition to:
[txtLastContact]<DateAdd("m",-6,Date()) And
[txtLastContact]>DateAdd("m",-12,Date())

and it now works!!! Not sure why it didn't like "DateAdd("y",-1,Date())" ???

 

[Guest]

Just realised my mistake with the DateAdd syntax. Should be:

DateAdd("yyyy",-1,Date())

to subtract 1 year. I had guessed a 'y' because the example in the Access
Help doesn't give years so this seemed logical when 'd' was day and 'm' was
month. Then looked it up via the VBA help and got the full syntax. Shame the
two don't correspond/link... Ho hum!

--
Peter Schmidt
Ross-on-Wye, UK

Changed the 2nd condition to:
[txtLastContact]<DateAdd("m",-6,Date()) And
[txtLastContact]>DateAdd("m",-12,Date())

and it now works!!! Not sure why it didn't like "DateAdd("y",-1,Date())" ???

--
Peter Schmidt
Ross-on-Wye, UK

I am trying to use conditional formatting to colour code a last contact date
field (txtLastContact) as follows:
Date falls within the last 6 months - green
Date is between 6 months and a year ago - orange
Date is more than 1 year ago - red

My 3 'Expression Is' conditions are as follows:
1) [txtLastContact]>=DateAdd("m",-6,Date())
2) [txtLastContact]<DateAdd("m",-6,Date()) And
[txtLastContact]>DateAdd("y",-1,Date())
3) [txtLastContact]<=DateAdd("y",-1,Date())

Expression 1 appears to work, but if the date is more than 6 months old it
goes red (when I am expecting it to be orange). I had also tried 'Field Value
Is' and 'Between' for the 2nd condition, but to no avail.

Thanks