condtional format

Question

I wish to conditionally format the most recent date of the field [Due Date] of a report of projects where each project will be showing related tasks.  IF it is greater than the present date, I would like to it to be conditionally formatted as Red.  My report is similar to this setup:

Project A

Task 1
Task 2
Task 3

Project B

Task 4
Task 5
Task 6

Project C

Task 7
Task 8
Task 9

Assuming that Tasks 3, 6, and 9 are the most recent dates, I would like conditionally format them based on their relation to the Present Date.   I can not think of an expression that would achieve this.  Is this possible?

Answer 1

Open the report in design mode

Select the field control you wish to format

Click Design > Conditional on the ribbon menu.

Answer 2

The following is an example of a conditional formatting expression for that, using Northwind data:

[Invoice Date]=DMax("[Invoice Date]","qryOrders","[Customer ID] = " & [Form].[Customer ID]) And [Invoice Date]>Date()

where qryOrders is the following query:

SELECT Orders.[Customer ID], Invoices.[Invoice Date]
FROM Orders INNER JOIN Invoices
ON Orders.[Order ID] = Invoices.[Order ID]
ORDER BY Orders.[Customer ID], Invoices.[Invoice Date];

Orders are analogous to your projects and invoices to your tasks.

PS: You'd change the reference to the [Form] object to a reference to the [Report] object of course.

Answer 3

Roger that, I will give this a shot.  Thanks so much.  Not sure why, but I was originally thinking of the Max() function.  the DMax() slipped my mind.

Answer 4

It doesn't seem to be working for me:  I have

[Start Date]=DMax("[Start Date]","[OpenInvestigations-Events]","[Start Date]= " & [Report].[Name] And [Start Date]<DAte())

Where OpenInvestigations-Events is the following query:



SELECT [Top Complainant] & "; " & [Name] AS [Case], [WRI Cases].Name, [WRI Cases].[Investigator (AI)], [WRI Cases].[Milestone Date], [WRI Cases].Statute, [WRI Cases].[Next Milestone], [WRI Cases].[AI Case Status], [WRI Cases].[Days in Investigation], [WRI Cases].[Top Complainant], [WRI Cases].[Current Case Status], [WRI Investigation].[WRI Investigation Event], [WRI Investigation].[Start Date], [WRI Investigation].[End Date], [WRI Investigation].Approval, [WRI Investigation].[Milestone Date]
FROM [WRI Cases] LEFT JOIN [WRI Investigation] ON [WRI Cases].Name = [WRI Investigation].[Related Case]
WHERE ((([WRI Cases].[AI Case Status])="Open - Investigation"));

Can you see anything that I am missing?

Answer 5

1.  I assume that the expression [Start Date]= " & [Report].[Name] should be [Name]= """ & [Report].[Name] & """")  Note that, as the Name column is presumably of text data type, its value needs to be wrapped in literal quotes characters.  Note also the closing parenthesis to the DMax function.  Consequently the final closing parenthesis after Date() should be omitted.

2.  You've used the < operator in the expression [Start Date]<Date().  You said that one criterion was that the date be 'greater than the present date', so the > operator should be used.

3.  Using Name as an object name is a bad idea.  As the name of a built in property, Name is a reserved keyword.  The resulting ambiguity might lead to the expression [Report].[Name] returning the Name property of the report, not the value in the column.  Use something like ComplainantName instead.

---

Ken Sheridan, Stafford, England

Answer 6

Thank you so much!  This solved my issue.  Although I made I few minor adjustments, your direction and assistance was spot on.