By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,285 Members | 2,124 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,285 IT Pros & Developers. It's quick & easy.

Help with DateDiff and Conditional Formatting

P: n/a
Hello Again Access GURU's,

Need some help with Conditional Formatting and datediff. What I am trying
to do is the following:

I have three text boxes with dates in them and what I want to do is change
the background color if the date in the text box get within a certain date
range.

Date Range:
21 Days out Backcolor Green
14 Days out Backcolor Yellow
07 Days out Backcoor Red

Example:

textbox1 has date of 08/28/2006
textbox2 has date of 08/21/2006
textbox3 has a date of 08/14/2006

=DateDiff("d",[textbox1],Date()) <-- Gives me 21
=DateDiff("d",[textbox2],Date()) <-- Gives me 14
=DateDiff("d",[textbox3],Date()) <-- Gives me 07

How do i set the backcolor of the textbox to different colors listed above
given the date range

Thanks,

Argus
Aug 7 '06 #1
Share this Question
Share on Google+
2 Replies


P: n/a
Bob
OdAwG wrote:
Hello Again Access GURU's,

Need some help with Conditional Formatting and datediff. What I am trying
to do is the following:

I have three text boxes with dates in them and what I want to do is change
the background color if the date in the text box get within a certain date
range.

Date Range:
21 Days out Backcolor Green
14 Days out Backcolor Yellow
07 Days out Backcoor Red

Example:

textbox1 has date of 08/28/2006
textbox2 has date of 08/21/2006
textbox3 has a date of 08/14/2006

=DateDiff("d",[textbox1],Date()) <-- Gives me 21
=DateDiff("d",[textbox2],Date()) <-- Gives me 14
=DateDiff("d",[textbox3],Date()) <-- Gives me 07

How do i set the backcolor of the textbox to different colors listed above
given the date range

Thanks,

Argus
Hi -

Maybe something similar to:

Private Sub Form_Open(Cancel As Integer)
Dim x As Integer

x = 1 + DateDiff("d", Date, DateHold) \ 7
Me.DateHold.BackColor = IIf(x 4, 65408, Choose(x, 16777215, 255,
65535, 65408))

End Sub

Aug 8 '06 #2

P: n/a
I tried the following and it did not work, actually, nothing happened. What
am I doing wrong
If [Textbox1] < 7 And [Textbox1] 0 Then
[Textbox1].BackColor = RED
ElseIf [Textbox1] < 14 And [Textbox1] 7 Then
[Textbox1].BackColor = Yellow
ElseIf [Textbox1] < 24 And [Textbox] 14 Then
[Textbox1].BackColor = Green
End if

"OdAwG" <Od***@goneloose.comwrote in message
news:vK*******************@tornado.texas.rr.com...
Hello Again Access GURU's,

Need some help with Conditional Formatting and datediff. What I am trying
to do is the following:

I have three text boxes with dates in them and what I want to do is change
the background color if the date in the text box get within a certain date
range.

Date Range:
21 Days out Backcolor Green
14 Days out Backcolor Yellow
07 Days out Backcoor Red

Example:

textbox1 has date of 08/28/2006
textbox2 has date of 08/21/2006
textbox3 has a date of 08/14/2006

=DateDiff("d",[textbox1],Date()) <-- Gives me 21
=DateDiff("d",[textbox2],Date()) <-- Gives me 14
=DateDiff("d",[textbox3],Date()) <-- Gives me 07

How do i set the backcolor of the textbox to different colors listed above
given the date range

Thanks,

Argus


Aug 8 '06 #3

This discussion thread is closed

Replies have been disabled for this discussion.