Changing format on lbl/txt depending on difference between two dates

[joobi]

What I have is basically a form, containing 1 label ("Lbl1") and 1 text box ("Txt1"). The text box is bound to a field in the table containing only dates (both future and past dates) with format YYYY-MM-DD.

What I need is the background color of both the label and the text box to turn red if the following condition is met:

The displayed date in the text box is less than 90 days from the current date. If the date in the text box is more than or equal to 90 days into the future the label and text box should keep their normal background colors which is blue for the label and yellow for the text box.

This check needs to be performed every time there is a record change in the form.

I guess I should use the "Date()" function to get today's date and then somehow subtract the current date from the date in the text box but I just can't figure out how to put the code together in practice.

Hope for some help with it.

 

[Mr. B]

You will need to use VBA code in the OnCurrent event of your form. In this code you can use the DateDiff function to calculate the difference in days between the current date and the date that is in your text box. Then you can use an IF statement to set the appropriate background color of your label and text box as desired.

Hope this helps point you in the right direction.

 

[boblarson]

Even simpler is to use Conditional Formatting (you just have to change your label to be a text box and then format it to look like a label and then you can use conditional formatting on each using the EXPRESSION IS and put in the Expression


DateDiff("d", [DateFieldNameHere], Date()) < 90

and then set the format you want it to be when it meets that.