Matrix Background Color

John (Yiannis) Toutountzoglou
John (Yiannis) Toutountzoglou used Ask the Experts™
on
Common Question But i can not understand what is going on!!
in My Matrix The Value TextBox is a date Field=FORMAT(MAX(Fields!Expr1.Value),"dd-MM-yyyy")
What i want to do is
1.when the value ="" then Color White
2.When Value Less than today Color Red
3.10 Days Before today Yellow..
Finnaly When Value>=Today Color Green
SO far ..
The Result Of my code is that there are no Greens Even if i have Values >today and i have yellows without any Values in Range
=IIF(FORMAT(MAX(Fields!Expr1.Value),"dd-MM-yyyy") = "","White",
     IIF(
         FORMAT(MAX(Fields!Expr1.Value),"dd-MM-yyyy")<Format(Today,"dd-MM-yyyy"),"Red",
                IIF(
                   FORMAT(MAX(Fields!Expr1.Value),"dd-MM-yyyy")<Format(DateAdd("d",-10,today),"dd-MM-yyyy"),"Yellow",
                    "Green"
       
        )            
      ) 
   )

Open in new window

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Glanced up at my screen and thought I had coded the Matrix...  Turns out, I just fell asleep on the keyboard.
Most Valuable Expert 2011
Top Expert 2015
Commented:
I feel your problem is that you are trying to compare strings versus actual dates. I would suggest changing the type of comparison. Withstanding the need for more information, I propose the following:
IIF(Fields!Expr1.Value IsNot Nothing AndAlso MAX(Fields!Expr1.Value).Length = 0,"White",
	IIF(CDate(MAX(Fields!Expr1.Value)) < Today, "Red",
		IIf(CDate(MAX(Fields!Expr1.Value)) < DateAdd("d", -10, Today), "Yellow", "Green")

Open in new window

John (Yiannis) ToutountzoglouInstructor Multiengine Pilot

Author

Commented:
You are very very right...Just a few Correction Because Length is not member of  expression i use Len instead..
And i Replace Is Not with Is
=IIF(Fields!Expr1.Value is Nothing AndAlso Len(Fields!Expr1.Value) = 0,"White",
	IIF(CDate(MAX(Fields!Expr1.Value)) < Today, "Red",
		IIf(CDate(MAX(Fields!Expr1.Value)) < DateAdd("d", -10, Today), "Yellow", "Green")))

Open in new window

John (Yiannis) ToutountzoglouInstructor Multiengine Pilot

Author

Commented:
Thank you very much Kaufmed
kaufmedGlanced up at my screen and thought I had coded the Matrix...  Turns out, I just fell asleep on the keyboard.
Most Valuable Expert 2011
Top Expert 2015

Commented:
NP.  Glad to help  :)

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial