Conditional formatting of dates in text box

[Ducatisto]

Hello,

I have searched and come across some results that are near to what I need but not quite. This is the scenario I have;
I have a training database that has a multitude of different training modules that have different expiration dates before retraining needs to take place. For instance one course does not need retraining, another needs retraining after 1 year, another after 3 years and so on. What my objective is for one month prior to expiry the text box will fill with amber and on expiry the text box to fill red. Is this at all possible?

 

[Alc]

Have a look at the DateAdd() function.

You could have code run when the form opens to compare today's date against the stored expiration date. If the difference is one month or less, set the colour of the field to amber, if the date has passed, to red.

 

[Dennisk]

The code would be best placed in the forms 'on current event' then it fires for each record and not once when the form opens.

 

[Alc]

The code would be best placed in the forms 'on current event' then it fires for each record and not once when the form opens.

I bow to your experience, sir

 

[Ducatisto]

I appreciate your prompt replies but I am afraid with Access I am a mere novice and as we speak still trying to sort it out! Is there a way (with relevant code) to conditionally format the text box that the date is entered into to achieve the results to which I need as per first post? Many thanks again.

 

[Alc]

Something like the following

If [I]expiration date[/I] <= Now() Then
       [I]fieldname[/I].BackColor = [I]code for red[/I]
ElseIf DateDiff("m", [I]expiration date[/I], Now()) >= 1 Then
       [I]fieldname[/I].BackColor = [I]code for amber[/I]
Else
       [I]fieldname[/I].BackColor = [I]code for white or whatever your normal[/I]
[I]                                     colour is[/I]
End If

 

[Alansidman]

Alc;
What would be the difference between your code and placing similar formula using the Conditional Format on the control? ie. Right click on the control and select conditional format. How would Access react differently in each case?
Alan

 

[Alc]

I honestly have no idea, I'm afraid.

The bulk of my Access knowledge (such as it is) has been self-taught - often with the help of the forum - as needed. As a result, there are many things I've never tried out and I often write code to do some things that may already be covered by in-built functionality.

 

[Alansidman]

Understand. I am in the same boat (self taught). In this situation, I would have opted for the conditional formatting function within Access. When I saw your post, I thought there was maybe a better mousetrap. Always learning new ways. Thanks

Alan

 

[Ducatisto]

Have a look at the DateAdd() function.

You could have code run when the form opens to compare today's date against the stored expiration date. If the difference is one month or less, set the colour of the field to amber, if the date has passed, to red.

I am still trying to find a solution but I will try to make clear what I currently have. The form I want to conditionally format takes information from a table, this holds, this holds 400+ training courses of which many need "refresher" courses. Within the table the time span in years is entered for which the refresher needs to be taken so in my subform one textbox has the date of the course and automatically in the next textbox the refresher year is entered by this code:

=DateAdd("yyyy",[refresherYears],[trainingDate])

So, if indeed the course was taken on December 12th 2005 and needed "refreshing" in 3 years the above code would enter 12/12/08. My aim was to get this text box fill to go amber if it was due in 1 months time and on equal or exceeding the date to go red. Can anyond advise me of the best way to go about this? Many thanks.

 

[Ducatisto]

Due to my "noobishness" in Access I am still unable to get a result of my liking, I can do it in Excel but thar is hardly a solution! I need to somehow translate what I have in Excel so as that Access will accept it as an Expression, here are the two formulas I use to determine due date (Red) and one month to due date (Amber);

Cell is less than

=DATE(YEAR(TODAY()),MONTH(TODAY()),DAY(TODAY()))

Due date.


Cell is less then

=DATE(YEAR(TODAY()),MONTH(TODAY())+1,DAY(TODAY()))

One month to due date.

How can I translate that to an Expression so as that Access accepts it?

Many Thanks.

 

[Ducatisto]

I would like to elaborate on my problem. I have a record subform with which trainers input a date as to when they completed a particular training course, the text box is titled 'trainingDate'. On another table I have a column titled 'RefresherYears' and this has differing timescales whether or not the courses need "refreshing". Going back to my subform and I have a text box titled that is titled 'Refresherdate' and in this text box has this formula

=DateAdd("yyyy",[refresherYears],[trainingDate])

.

At the moment ALL Refresherdate cells just show as grey but my aim is that once a trainer enters a date in trainingDate and in refresherYears there is 0 entered the cell in Refresherdate will remain grey, but if there has been 1, 2, 3 etc entered into the refresherYears due to the formula above a date will be entered 1, 2, 3 years etc on from the trainingDate. For instance 15/12/08 was entered by the trainer in trainingDate and the course has a 3 year refresher timespan (as per refresherYears) within Refresherdate a date of 15/12/11 will appear. My aim is for those cells that are equal to or past the date to go red and also would like a one month "warning", so that any date that is within one month of a Refresherdate for the cell to go amber. Any help will be gratefully received.

 

[Alansidman]

Look at the form 2 on the attached. Look at the properties for the text box and the conditional formatting. It should lead you in the right direction.

 

[Ducatisto]

Look at the form 2 on the attached. Look at the properties for the text box and the conditional formatting. It should lead you in the right direction.

Thank you for that, it seemed as though it was the right way to go but it has not worked in my case, back to the drawing board!