Change color on date field in form for re-certification

[messingerjc]

Hello. I have two different date fields on a form, but I would like them to
change color depending on how close to the re-certification date it is
whenever I open the form or open a report showing the same info. On each of
the date fields, I would like to have them green for anything over a month
away, yellow for anytime within a month, and red for anything expired. One
date field [PTDate] needs re-certified every 6 months, and the other date
field [WeaponDate] needs re-certified every 12 months. I've been struggling
with this for several days now and haven't been able to figure it out. Any
help on this would be greatly appreciated! Thank you in advance!

 

[John W. Vinson]

Hello. I have two different date fields on a form, but I would like them to
change color depending on how close to the re-certification date it is
whenever I open the form or open a report showing the same info. On each of
the date fields, I would like to have them green for anything over a month
away, yellow for anytime within a month, and red for anything expired. One
date field [PTDate] needs re-certified every 6 months, and the other date
field [WeaponDate] needs re-certified every 12 months. I've been struggling
with this for several days now and haven't been able to figure it out. Any
help on this would be greatly appreciated! Thank you in advance!

Depending on your version of Access, you should be able to use Conditional
Formatting. Use an expression such as

DateAdd("m", 5, [PTDate]) > Date()

for green,

DateAdd("m", 5, [PTDate]) < Date() AND DateAdd("m", 6, [PTDate]) >= Date()

for yellow, and so on.