Change color on date field in form for re-certification

[messingerjc]

My main form has two different date fields on it. One date field needs to be
re-certified every 6 months, and the other field needs to be re-certified
every 12 months. I would like each of those fields to automatically change
color depending on how much time as elapsed since that training. For the 6
month field, months 1-5 should be green, 1 month left should be yellow and
anything expired should be red. For the 12 month field, months 1-11 should be
green, 1 month left should be yellow, and anything expired should be red.

Thank you in advance for your help!

 

[Marshall Barton]

My main form has two different date fields on it. One date field needs to be
re-certified every 6 months, and the other field needs to be re-certified
every 12 months. I would like each of those fields to automatically change
color depending on how much time as elapsed since that training. For the 6
month field, months 1-5 should be green, 1 month left should be yellow and
anything expired should be red. For the 12 month field, months 1-11 should be
green, 1 month left should be yellow, and anything expired should be red.

Try setting the text box's fore or back color to Red as the
default situation.

Then use the Format menu - Conditional Foramtting. Select
the Expression Is: option and use an expression like:
DateDiff("m", [date field], Date()) <= 5
and select green as the fore or back color.

To get yellow for the 6th month, add another condition like
the above with the expression:
DateDiff("m", [date field], Date()) <= 6

 

[messingerjc]

Sorry for the delay in responding back, I've been extremely busy at work.
That worked! Outstanding! Thank you for your help!

My main form has two different date fields on it. One date field needs to be
re-certified every 6 months, and the other field needs to be re-certified
every 12 months. I would like each of those fields to automatically change
color depending on how much time as elapsed since that training. For the 6
month field, months 1-5 should be green, 1 month left should be yellow and
anything expired should be red. For the 12 month field, months 1-11 should be
green, 1 month left should be yellow, and anything expired should be red.

Try setting the text box's fore or back color to Red as the
default situation.

Then use the Format menu - Conditional Foramtting. Select
the Expression Is: option and use an expression like:
DateDiff("m", [date field], Date()) <= 5
and select green as the fore or back color.

To get yellow for the 6th month, add another condition like
the above with the expression:
DateDiff("m", [date field], Date()) <= 6

 

[messingerjc]

I take it back. That worked for the 6 month re-certification, but I ran into
a problem with the 12 month re-certification. Because of that coding, Access
isn't looking at the year, it's looking at the month, therefore, it's calling
Feb. 09 the same as Feb. 10 and comparing it to the current year and is
returning the field as being good since it's less than 12 months ago.

Sorry for the delay in responding back, I've been extremely busy at work.
That worked! Outstanding! Thank you for your help!

My main form has two different date fields on it. One date field needs to be
re-certified every 6 months, and the other field needs to be re-certified
every 12 months. I would like each of those fields to automatically change
color depending on how much time as elapsed since that training. For the 6
month field, months 1-5 should be green, 1 month left should be yellow and
anything expired should be red. For the 12 month field, months 1-11 should be
green, 1 month left should be yellow, and anything expired should be red.

Try setting the text box's fore or back color to Red as the
default situation.

Then use the Format menu - Conditional Foramtting. Select
the Expression Is: option and use an expression like:
DateDiff("m", [date field], Date()) <= 5
and select green as the fore or back color.

To get yellow for the 6th month, add another condition like
the above with the expression:
DateDiff("m", [date field], Date()) <= 6

 

[Marshall Barton]

It's looking at the year alright, but is not looking at
partial months.

If you want to know the difference in full months for one
date relative to another date, try using an expression like:

DateDiff("m", dateA, dateB) + Int( Day(dateB) < Day(dateA) )
--
Marsh
MVP [MS Access]

I take it back. That worked for the 6 month re-certification, but I ran into
a problem with the 12 month re-certification. Because of that coding, Access
isn't looking at the year, it's looking at the month, therefore, it's calling
Feb. 09 the same as Feb. 10 and comparing it to the current year and is
returning the field as being good since it's less than 12 months ago.

Sorry for the delay in responding back, I've been extremely busy at work.
That worked! Outstanding! Thank you for your help!

messingerjc wrote:
My main form has two different date fields on it. One date field needs to be
re-certified every 6 months, and the other field needs to be re-certified
every 12 months. I would like each of those fields to automatically change
color depending on how much time as elapsed since that training. For the 6
month field, months 1-5 should be green, 1 month left should be yellow and
anything expired should be red. For the 12 month field, months 1-11 should be
green, 1 month left should be yellow, and anything expired should be red.


Try setting the text box's fore or back color to Red as the
default situation.

Then use the Format menu - Conditional Foramtting. Select
the Expression Is: option and use an expression like:
DateDiff("m", [date field], Date()) <= 5
and select green as the fore or back color.

To get yellow for the 6th month, add another condition like
the above with the expression:
DateDiff("m", [date field], Date()) <= 6

 

[Mike Painter]

I take it back. That worked for the 6 month re-certification, but I
ran into a problem with the 12 month re-certification. Because of
that coding, Access isn't looking at the year, it's looking at the
month, therefore, it's calling Feb. 09 the same as Feb. 10 and
comparing it to the current year and is returning the field as being
good since it's less than 12 months ago.

You asked for months and that was the solution given.

I use calculated fields to get the expiration date and use 60 days before
the event for color coding.
The field turns yellow xx days before and red when it expires.

That's handy but I also have a report that lets me fill in any number of
days before expiration and one that just shows expired.