Z
ZenManji
Hi all, and thank you ahead of time for any light that you might be
able to shine on this issue that I'm having.
What I have now are a series of text boxes that contain dates at
certain intervals away from a base date. So, I have 6 boxes, the
first being the baseline date that the other following 5 dates (boxes)
are coded off of. I used the Expression Builder to generate the date
for those five boxes. An example being:
=DateSerial(Year([BaselineDate]),Month([BaselineDate]),Day([BaselineDate])
+14)
This is for the first box after the baseline date, at 2 weeks out.
The others are at 3 months, 6 months, 1 year, and 2 years. All of
that is working just fine.
The issue that I am tackling now is this: I am trying to get the
background of the 5 boxes to change to specific colors as the present
date gets closer or further away from the date in each on of the
boxes. Essentially, the dates each have a before and after "window"
of the target date. For example, the 3-month date has a window of 1
month before and 1 month after. So ideally, I would like the text box
containing the 3-month date to change to green when the window is open
(and on the target date), and then change to yellow when the window is
closing (the latter month), and then change to red when nearing the
end of the window by 2 weeks.
Since it is three states that I'm trying to generate, and the
Conditional Formatting tool offers three conditions, I started with
that. I can get the Green and Yellow states to appear using:
[Text255]>=Date() and [Text255]<Date(). But that of course is not
restricted to the time window, just before and after. I tried using
DateAdd, DateDiff, and DateSerial in different ways, but to no
avail.
If anyone can help me with the proper code for this, at least by
pointing me in the right direction, I'd greatly appreciate it!!
Note: I haven't ruled out Code builder, but I'm not sure what event to
use if I want the code to run immediately and all of the time while
the form is being viewed.
able to shine on this issue that I'm having.
What I have now are a series of text boxes that contain dates at
certain intervals away from a base date. So, I have 6 boxes, the
first being the baseline date that the other following 5 dates (boxes)
are coded off of. I used the Expression Builder to generate the date
for those five boxes. An example being:
=DateSerial(Year([BaselineDate]),Month([BaselineDate]),Day([BaselineDate])
+14)
This is for the first box after the baseline date, at 2 weeks out.
The others are at 3 months, 6 months, 1 year, and 2 years. All of
that is working just fine.
The issue that I am tackling now is this: I am trying to get the
background of the 5 boxes to change to specific colors as the present
date gets closer or further away from the date in each on of the
boxes. Essentially, the dates each have a before and after "window"
of the target date. For example, the 3-month date has a window of 1
month before and 1 month after. So ideally, I would like the text box
containing the 3-month date to change to green when the window is open
(and on the target date), and then change to yellow when the window is
closing (the latter month), and then change to red when nearing the
end of the window by 2 weeks.
Since it is three states that I'm trying to generate, and the
Conditional Formatting tool offers three conditions, I started with
that. I can get the Green and Yellow states to appear using:
[Text255]>=Date() and [Text255]<Date(). But that of course is not
restricted to the time window, just before and after. I tried using
DateAdd, DateDiff, and DateSerial in different ways, but to no
avail.
If anyone can help me with the proper code for this, at least by
pointing me in the right direction, I'd greatly appreciate it!!
Note: I haven't ruled out Code builder, but I'm not sure what event to
use if I want the code to run immediately and all of the time while
the form is being viewed.