3 Conditional formats

[Guest]

Target Date is
my control source
=DateAdd("m",-2,[Forms]![frmProjects]![BuildDate])

I need some help I can't figure out the field value less than, between or
??...
what I am trying to do is:

when I am within 30 days or less of the builddate turn yellow
DateAdd("d",-30,Forms!frmProjects!BuildDate)

within 14 days or less I want to turn maroon
DateAdd("d",-14,Forms!frmProjects!BuildDate)

But if the target date is earlier than calculated date from the build date
it has expired red

Can someone help me I keep trying different combinations but the -# is
making me crazy.
Thanks

 

[Wayne Morgan]

Just to verify with the way you've worded this. "when I am within 30 days or
less of the builddate", if you mean the date in the textbox, it will never
be. It will be 2 months from the BuildDate, that is what you're Control
Source is calculating. If you mean today's date, then this can be done.

Set the drop down to Expression Is. Next, in the box to the right of the
drop down enter:

Date()>=DateAdd("d",-30,Forms!frmProjects!BuildDate) And
Date()<DateAdd("d",-14,Forms!frmProjects!BuildDate)

and select Yellow, be aware Yellow may be hard to see.

For the next one,

Date()>=DateAdd("d",-14,Forms!frmProjects!BuildDate)

and choose maroon.

Now, for the last one, "But if the target date is earlier than calculated
date from the build date it has expired red", I don't understand. You said
the "target date" was "=DateAdd("m",-2,[Forms]![frmProjects]![BuildDate])".
There is nothing here that will allow it to ever be other than 2 months
before the BuildDate. Please explain further.

 

[Guest]

Thanks... Your response helps me to see where I was going wrong. I get it now!

Just to verify with the way you've worded this. "when I am within 30 days or
less of the builddate", if you mean the date in the textbox, it will never
be. It will be 2 months from the BuildDate, that is what you're Control
Source is calculating. If you mean today's date, then this can be done.

Set the drop down to Expression Is. Next, in the box to the right of the
drop down enter:

Date()>=DateAdd("d",-30,Forms!frmProjects!BuildDate) And
Date()<DateAdd("d",-14,Forms!frmProjects!BuildDate)

and select Yellow, be aware Yellow may be hard to see.

For the next one,

Date()>=DateAdd("d",-14,Forms!frmProjects!BuildDate)

and choose maroon.

Now, for the last one, "But if the target date is earlier than calculated
date from the build date it has expired red", I don't understand. You said
the "target date" was "=DateAdd("m",-2,[Forms]![frmProjects]![BuildDate])".
There is nothing here that will allow it to ever be other than 2 months
before the BuildDate. Please explain further.

--
Wayne Morgan
MS Access MVP

Target Date is
my control source
=DateAdd("m",-2,[Forms]![frmProjects]![BuildDate])

I need some help I can't figure out the field value less than, between or
??...
what I am trying to do is:

when I am within 30 days or less of the builddate turn yellow
DateAdd("d",-30,Forms!frmProjects!BuildDate)

within 14 days or less I want to turn maroon
DateAdd("d",-14,Forms!frmProjects!BuildDate)

But if the target date is earlier than calculated date from the build date
it has expired red

Can someone help me I keep trying different combinations but the -# is
making me crazy.
Thanks