Conditional Formatting

[despatcher]

Hi Guys,

Having some real difficulties with conditional formatting i know this must be simple but i think i am getting them in the wrong order.

What i want to do is colour code certain boxes dependant on the date input

The Date someone qualifies will be entered into the box

If the date exceeds todays date by two years i want the box to turn RED

30 Days before it turns RED i want it to turn AMBER

90 Days before it turns RED i want it to turn GREEN

My attempt was the following:

between Date()-275 Date()-334 GREEN

between Date()-335 and Date()-364 = AMBER

Greater than Date()-365 = RED

But this didn't seem to be correct any help would be appreciated

 

[missinglinq]

First off I think you need to clarify your goals! You say

If the date exceeds todays date by two years i want the box to turn RED

then you proceed to do your calculations for the other colors based on one year not two! Which is it? And what do you want to do if your original condition, whether one year or two, is not met?

 

[despatcher]

Mingling reply

What i have is a database containing qualification dates for my personnel after one year from the date they qualify in a subject they have to do a re-course.

What i have been asked to do is apply a form of traffic light system to allow line managers to quickly View / Visualise (They do not want a report) when there employees are due to need re-coursed.

3 months / 90 days before the employees qualification expires they would like the box the date is contained in to turn from the normal white box to a green box that still contains the date.

1 month / 30 days before the employees qualification expires they would like the box the date is contained in to turn from the normal white box to an amber box that still contains the date.

Once the full year has lapsed then the box would turn RED to signal that this person no longer holds that qualification.

Once they have attended a course of instruction the new date will be put into the box and it would turn the normal white and the process will begin again.

Hope this clears things up if not please ask again and i will try to clarify further

All help is appreciated

Sorry just clarify its one year and i got your name wrong sorry Missinglinq hope you can help

 

[despatcher]

Getting Desperate

I have had a new boss who wants me to change the database i created over a year ago. At that time i had just done an Access course and picked up some good tips along the way since then i have hardly touched the programme until now.

I set the conditional formatting like this because when someone came back from a course i told line managers to add a year to the date there employees passed enter the date onto the db and treated this date as a run out date if you like.

The conditional format worked a treat

Default formatting = White

Between Date() and Date()+14 = Amber

Between Date() and Date ()+43 = Green

Less than Date() = Red

Problem NEW BOSS wants the date they actually passed the course input to the db but wants the colours to remain in place only with longer warning times 4 weeks instead of two 12 weeks instead of six. (each course is only valid for a year)

I tried to reverse these with varying degrees of success but cannot seem to get it.

Sorry to pester you but he wants this resolved tomorrow as silly me told him it should be quite simple.

Which i believe it must be by i just cant get it.

Thanks in advance

 

[missinglinq]

I think this does what you're after. I've done some testing but am out of time here; you can do more in depth testing and tweak if necessary! In the Conditional Format box place each of these using the Expression Is option, replacing [certdate] with whatever your actual control name is.

'Set Background Green
Now()>=[certdate]+275 And Now()<=[certdate]+335

'Set Background Amber
Now()>=[certdate]+336 And Now()<=[certdate]+365

'Set Background Red
Now()>=[certdate]+365

Good Luck!