Conditional formatting based on a date

[speaton via AccessMonster.com]

Hello, everyone.

I have a subreport that is used to list comments made by various individuals
according to when they made them, and recently I've had a request to change
the color of comments entered into the system in the past week to blue so
that anyone using the report can skip immediately to the blue comments to see
the most recent ones.

The subreport itself has controls named User, CommentDate, Comment, and
Sentiment. The table from which this information is drawn also has a field
named EntryDate. What I would like to do is have User, CommentDate, Comment,
and Sentiment appear in blue if EntryDate is greater than or equal to Date()-
7, but if possible I would prefer for EntryDate not to be shown on the
subreport.

Can anyone recommend a good way to achieve this effect?

This is being done in Access 2007, if that makes any difference.

With many thanks for any and all pointers,

speaton

 

[Marshall Barton]

I have a subreport that is used to list comments made by various individuals
according to when they made them, and recently I've had a request to change
the color of comments entered into the system in the past week to blue so
that anyone using the report can skip immediately to the blue comments to see
the most recent ones.

The subreport itself has controls named User, CommentDate, Comment, and
Sentiment. The table from which this information is drawn also has a field
named EntryDate. What I would like to do is have User, CommentDate, Comment,
and Sentiment appear in blue if EntryDate is greater than or equal to Date()-
7, but if possible I would prefer for EntryDate not to be shown on the
subreport.

Can anyone recommend a good way to achieve this effect?

This is being done in Access 2007, if that makes any difference.

Set the comment text box's CF expression to something like:
[EntryDate] > Date() - 7

The EntryDate field must be in the report's record source
table/query and usually you need it to be bound to another
text box in the report. If you do need a text box bound to
the DateEntry field, then just make the text box invisible.

 

[John Spencer]

Include entry date in the underlying query for the sub-report. You can add it
to the detail line to a bound control and set the visible property of the
control to false.

Now you should be able to use an expression in conditional formatting to
change the background of the other controls to blue.

Expression is: [Entry Date] >= Date()-7

You may be able to avoid including the control bound to the Entry Date field,
but Access may then drop the field from the query it builds in the background
for reports. You can experiment by including the Entry Date in the underlying
record source and see if things work. If not add a control, assign Entry Date
as its source, and set the control's visible property to No (false).

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

 

[speaton via AccessMonster.com]

Marshall, John,

Thank you both very much for your responses!

I thought it would be something like that, but wasn't having any luck with it
yesterday. As it turns out, I was simply neglecting to set the formatting for
cases where the controls do not match that condition. Now that I've done that,
it works perfectly.

Thanks again!

speaton