Prev: sending sms directly from MS Access
Next: Problem with closing from from Close Button
From: messingerjc on 14 Apr 2010 10:29
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!
From: Marshall Barton on 14 Apr 2010 12:49
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

--
Marsh
MVP [MS Access]
From: messingerjc on 16 Apr 2010 10:21
Sorry for the delay in responding back, I've been extremely busy at work.
That worked! Outstanding! Thank you for your help!

"Marshall Barton" wrote:

> 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
>
> --
> Marsh
> MVP [MS Access]
> .
>
From: messingerjc on 17 Apr 2010 17:38
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.

"messingerjc" wrote:

> Sorry for the delay in responding back, I've been extremely busy at work.
> That worked! Outstanding! Thank you for your help!
>
> "Marshall Barton" wrote:
>
> > 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
> >
> > --
> > Marsh
> > MVP [MS Access]
> > .
> >
From: Marshall Barton on 17 Apr 2010 19:48
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]


messingerjc wrote:
>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.
>
>"messingerjc" wrote:
>> Sorry for the delay in responding back, I've been extremely busy at work.
>> That worked! Outstanding! Thank you for your help!
>>
>> "Marshall Barton" wrote:
>> > 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
 |  Next  |  Last
Pages: 1 2
Prev: sending sms directly from MS Access
Next: Problem with closing from from Close Button