Conditional formats based on time

Hello,

I'm trying to have a cell in a report turn red if the item is still active
in a data base. In other words. I have an action register. for my report, I
filter out everything that is not "in progress" I have a "Date entered"
field. What I would like is if the item is still "in progress" 60 days after
teh "date entered" I would like the report line to turn red.

Any help would be appreciated!
--
Thank you for your time!
John

In the "on format" event of the detail section enter something along the
lines of the following code:

If Me.[DateEntered] Date()-60 then

me.detail.backcolor = 255

end if


"Very Basic User" wrote:

Hello,

I'm trying to have a cell in a report turn red if the item is still active
in a data base. In other words. I have an action register. for my report, I
filter out everything that is not "in progress" I have a "Date entered"
field. What I would like is if the item is still "in progress" 60 days after
teh "date entered" I would like the report line to turn red.

Any help would be appreciated!
--
Thank you for your time!
John

I DANT SPEAK ENGLECH I M SPEAK FRENCH

A few problems with that:

1. Its better to use the DateAdd function than simple date arithmetic,
which places reliance on the implementation, something good programming
practice should avoid.

2. Once the BackColor property is set to red it will stay that way for
subsequent rows unless reset to white.

3. To make the whole row red its also necessary to set the BackColor
property of each of its controls as well as that of the section.

So the code for the detail section's Format event procedure would be:

Dim sec As Section
Dim ctrl As Control

Set sec = Me.Section(acDetail)

If Me.[Date Entered] = DateAdd("d",-60,VBA.Date) Then
sec.BackColor = vbRed
For Each ctrl In sec.Controls
ctrl.BackColor = vbRed
Next ctrl
Else
sec.BackColor = vbWhite
For Each ctrl In sec.Controls
ctrl.BackColor = vbWhite
Next ctrl
End If

Ken Sheridan
Stafford, England

"Adam Milligan" wrote:

In the "on format" event of the detail section enter something along the
lines of the following code:

If Me.[DateEntered] Date()-60 then

me.detail.backcolor = 255

end if


"Very Basic User" wrote:

Hello,

I'm trying to have a cell in a report turn red if the item is still active
in a data base. In other words. I have an action register. for my report, I
filter out everything that is not "in progress" I have a "Date entered"
field. What I would like is if the item is still "in progress" 60 days after
teh "date entered" I would like the report line to turn red.

Any help would be appreciated!
--
Thank you for your time!
John

Ken-

Thanks for chiming on on that one. Those are things I would have
(obviously) run into once I tested it and then smacked my forehead and said
"duh!". It's a lot more difficult to just throw code out to people in need
and get it right the first time. I really respect the patience and
knopwledge you MVPs show everyone around here. Thanks.

Adam Milligan

"Ken Sheridan" wrote:

A few problems with that:

1. Its better to use the DateAdd function than simple date arithmetic,
which places reliance on the implementation, something good programming
practice should avoid.

2. Once the BackColor property is set to red it will stay that way for
subsequent rows unless reset to white.

3. To make the whole row red its also necessary to set the BackColor
property of each of its controls as well as that of the section.

So the code for the detail section's Format event procedure would be:

Dim sec As Section
Dim ctrl As Control

Set sec = Me.Section(acDetail)

If Me.[Date Entered] = DateAdd("d",-60,VBA.Date) Then
sec.BackColor = vbRed
For Each ctrl In sec.Controls
ctrl.BackColor = vbRed
Next ctrl
Else
sec.BackColor = vbWhite
For Each ctrl In sec.Controls
ctrl.BackColor = vbWhite
Next ctrl
End If

Ken Sheridan
Stafford, England

"Adam Milligan" wrote:

In the "on format" event of the detail section enter something along the
lines of the following code:

If Me.[DateEntered] Date()-60 then

me.detail.backcolor = 255

end if


"Very Basic User" wrote:

Hello,

I'm trying to have a cell in a report turn red if the item is still active
in a data base. In other words. I have an action register. for my report, I
filter out everything that is not "in progress" I have a "Date entered"
field. What I would like is if the item is still "in progress" 60 days after
teh "date entered" I would like the report line to turn red.

Any help would be appreciated!
--
Thank you for your time!
John

Now if I only wanted to turn the date red, is there a simple conditional
formatting line that I could use?
--
Thank you for your time!
John


"Adam Milligan" wrote:

Ken-

Thanks for chiming on on that one. Those are things I would have
(obviously) run into once I tested it and then smacked my forehead and said
"duh!". It's a lot more difficult to just throw code out to people in need
and get it right the first time. I really respect the patience and
knopwledge you MVPs show everyone around here. Thanks.

Adam Milligan

"Ken Sheridan" wrote:

A few problems with that:

1. Its better to use the DateAdd function than simple date arithmetic,
which places reliance on the implementation, something good programming
practice should avoid.

2. Once the BackColor property is set to red it will stay that way for
subsequent rows unless reset to white.

3. To make the whole row red its also necessary to set the BackColor
property of each of its controls as well as that of the section.

So the code for the detail section's Format event procedure would be:

Dim sec As Section
Dim ctrl As Control

Set sec = Me.Section(acDetail)

If Me.[Date Entered] = DateAdd("d",-60,VBA.Date) Then
sec.BackColor = vbRed
For Each ctrl In sec.Controls
ctrl.BackColor = vbRed
Next ctrl
Else
sec.BackColor = vbWhite
For Each ctrl In sec.Controls
ctrl.BackColor = vbWhite
Next ctrl
End If

Ken Sheridan
Stafford, England

"Adam Milligan" wrote:

In the "on format" event of the detail section enter something along the
lines of the following code:

If Me.[DateEntered] Date()-60 then

me.detail.backcolor = 255

end if


"Very Basic User" wrote:

Hello,

I'm trying to have a cell in a report turn red if the item is still active
in a data base. In other words. I have an action register. for my report, I
filter out everything that is not "in progress" I have a "Date entered"
field. What I would like is if the item is still "in progress" 60 days after
teh "date entered" I would like the report line to turn red.

Any help would be appreciated!
--
Thank you for your time!
John

Stealing Ken's code below try this...

If Me.[Date Entered] = DateAdd("d",-60,VBA.Date) Then
me.[Date Entered].forecolor = vbRed
Else
me.[Date Entered].forecolor= vbBlack
End If


P.S. I find it very useful to NEVER put spaces in my field names
"Very Basic User" wrote:

Now if I only wanted to turn the date red, is there a simple conditional
formatting line that I could use?
--
Thank you for your time!
John


"Adam Milligan" wrote:

Ken-

Thanks for chiming on on that one. Those are things I would have
(obviously) run into once I tested it and then smacked my forehead and said
"duh!". It's a lot more difficult to just throw code out to people in need
and get it right the first time. I really respect the patience and
knopwledge you MVPs show everyone around here. Thanks.

Adam Milligan

"Ken Sheridan" wrote:

A few problems with that:

1. Its better to use the DateAdd function than simple date arithmetic,
which places reliance on the implementation, something good programming
practice should avoid.

2. Once the BackColor property is set to red it will stay that way for
subsequent rows unless reset to white.

3. To make the whole row red its also necessary to set the BackColor
property of each of its controls as well as that of the section.

So the code for the detail section's Format event procedure would be:

Dim sec As Section
Dim ctrl As Control

Set sec = Me.Section(acDetail)

If Me.[Date Entered] = DateAdd("d",-60,VBA.Date) Then
sec.BackColor = vbRed
For Each ctrl In sec.Controls
ctrl.BackColor = vbRed
Next ctrl
Else
sec.BackColor = vbWhite
For Each ctrl In sec.Controls
ctrl.BackColor = vbWhite
Next ctrl
End If

Ken Sheridan
Stafford, England

"Adam Milligan" wrote:

In the "on format" event of the detail section enter something along the
lines of the following code:

If Me.[DateEntered] Date()-60 then

me.detail.backcolor = 255

end if


"Very Basic User" wrote:

Hello,

I'm trying to have a cell in a report turn red if the item is still active
in a data base. In other words. I have an action register. for my report, I
filter out everything that is not "in progress" I have a "Date entered"
field. What I would like is if the item is still "in progress" 60 days after
teh "date entered" I would like the report line to turn red.

Any help would be appreciated!
--
Thank you for your time!
John

This is exactly what I need, I also figured out your original code
understanding now that [Date Entered] needed to be typed exactly as my field
was written. Thank you very much for your help and support!
--
Thank you for your time!
John


"Adam Milligan" wrote:

Stealing Ken's code below try this...

If Me.[Date Entered] = DateAdd("d",-60,VBA.Date) Then
me.[Date Entered].forecolor = vbRed
Else
me.[Date Entered].forecolor= vbBlack
End If


P.S. I find it very useful to NEVER put spaces in my field names
"Very Basic User" wrote:

Now if I only wanted to turn the date red, is there a simple conditional
formatting line that I could use?
--
Thank you for your time!
John


"Adam Milligan" wrote:

Ken-

Thanks for chiming on on that one. Those are things I would have
(obviously) run into once I tested it and then smacked my forehead and said
"duh!". It's a lot more difficult to just throw code out to people in need
and get it right the first time. I really respect the patience and
knopwledge you MVPs show everyone around here. Thanks.

Adam Milligan

"Ken Sheridan" wrote:

A few problems with that:

1. Its better to use the DateAdd function than simple date arithmetic,
which places reliance on the implementation, something good programming
practice should avoid.

2. Once the BackColor property is set to red it will stay that way for
subsequent rows unless reset to white.

3. To make the whole row red its also necessary to set the BackColor
property of each of its controls as well as that of the section.

So the code for the detail section's Format event procedure would be:

Dim sec As Section
Dim ctrl As Control

Set sec = Me.Section(acDetail)

If Me.[Date Entered] = DateAdd("d",-60,VBA.Date) Then
sec.BackColor = vbRed
For Each ctrl In sec.Controls
ctrl.BackColor = vbRed
Next ctrl
Else
sec.BackColor = vbWhite
For Each ctrl In sec.Controls
ctrl.BackColor = vbWhite
Next ctrl
End If

Ken Sheridan
Stafford, England

"Adam Milligan" wrote:

In the "on format" event of the detail section enter something along the
lines of the following code:

If Me.[DateEntered] Date()-60 then

me.detail.backcolor = 255

end if


"Very Basic User" wrote:

Hello,

I'm trying to have a cell in a report turn red if the item is still active
in a data base. In other words. I have an action register. for my report, I
filter out everything that is not "in progress" I have a "Date entered"
field. What I would like is if the item is still "in progress" 60 days after
teh "date entered" I would like the report line to turn red.

Any help would be appreciated!
--
Thank you for your time!
John