May 11, 2018 at 1:07 pm
I'm not a programmer by trade. Fair warning....lol
I'm struggling with writing an IFF statement that will allow the field color to be one of three colors. If the value returned is <15 I need the field to reflect as "white." If the value is between 15 and 30 I need the field to reflect as "yellow." If the value is >30 I need the field to reflect as "red."
I'm not sure the below statement is even close to being accurate. Please help.
=IFF((datediff(“d”,Fields!Case_Start_Date.Value,now())>30,"Red",(IFF(datediff(“d”,Fields!Case_Start_Date.Value,now()))>= 15 AND <= 30,"Yellow",(IFF(datediff(“d”,Fields!Case_Start_Date.Value,now())) <15,"White"))))
May 13, 2018 at 8:05 am
Your post suggests you haven't tried it yet. Have you? If not, do! If you have, I assume it's not working; what about it isn't working?
(I will, however, suggest you don't need 3 IIF's; you only need 2.)
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
May 13, 2018 at 9:42 pm
I have tried it. I didn't work. I've tried a lot of variations and still can't get it to color code correctly.
I've also tried:
IIF(("d",Fields!NextActionDueBy.Value<today(),"red", IIF("d",Fields!NextActionDueBy.Value>=now()+30,Fields!NextActionDueBy.Value+60),"yellow")
But it keeps giving an error:
"is not a valid color"
May 14, 2018 at 2:24 am
YOU're close, but not quite there; seems you don't quite understand how an IIF works, or nested IIF's, so I'll explain that first.
The format of an IIF works like so:=IIF(<<boolean expression>>, <<value if true>>, <<value if false>>)
So, in very simple terms:=IIF(Fields!integer.Value = 1, "Success", "Failure")
This would return the value "Success" if integer.value has a value of 1, otherwise "Failure". If you have nested IIF's, the format looks more like this:=IIF(<<boolean expression 1>>, <<value if expr1 true>>, IIF(<<boolean expression 2>>, <<value if expr2 true>>,IIF(<<boolean expression 3>>, <<value if expr3 true>>, <<value if all expr false)))
Notice that I've state the final value is if all expressions are false, not just the third.
Like I said in my initial reply, as you only have 3 values, you only need 2 IIFs,. The number of IIFSs you need is effectively Number of possible Values - 1. So your expression would be:=IIf(DateDiff("d",Fields!Case_Start_Date.Value,Today()) > 30,"Red",IIf(DateDiff("d",Fields!Case_Start_Date.Value,Today()) >= 15 AND DateDiff("d",Fields!Case_Start_Date.Value,Today()) <= 30,"Yellow","White")))
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
May 14, 2018 at 2:13 pm
@scott.barrett 23151
In your post above you have IFF instead of IIF
May 14, 2018 at 2:28 pm
Joe Torre - Monday, May 14, 2018 2:13 PM@scott.barrett 23151
In your post above you have IFF instead of IIF
That's not the only problem in their original post. In the final IIF (or IFF), they only have 2 parameters, the boolean expression and the expression when the result is true; there's no expression for if the boolean expression evaluates to false.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
May 15, 2018 at 8:25 am
You are correct that I didn't understand the formula for writing IIF statements. I'm not an IT person by trade and have been trying to learn how to generate SQL reports on my own as our local IT techs aren't very much help.
When I used the IIF example you gave me, I receive the following error: The BackgroundColor expression for the text box ‘Textbox4’ contains an error: [BC30205] End of statement expected. The definition of the report '' is invalid.
I have a variety of IIF statements I need to figure out so the report color codes correctly. The problem is most of the fields revolve around dates and the number of days between today and the due date.
May 15, 2018 at 8:28 am
scott.barrett 23151 - Tuesday, May 15, 2018 8:25 AMYou are correct that I didn't understand the formula for writing IIF statements. I'm not an IT person by trade and have been trying to learn how to generate SQL reports on my own as our local IT techs aren't very much help.When I used the IIF example you gave me, I receive the following error: The BackgroundColor expression for the text box ‘Textbox4’ contains an error: [BC30205] End of statement expected. The definition of the report '' is invalid.
I have a variety of IIF statements I need to figure out so the report color codes correctly. The problem is most of the fields revolve around dates and the number of days between today and the due date.
And what is the BackgroundColor expression of textbox4?
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
May 15, 2018 at 11:27 am
Thom A - Monday, May 14, 2018 2:24 AMLike I said in my initial reply, as you only have 3 values, you only need 2 IIFs,. The number of IIFSs you need is effectively Number of possible Values - 1. So your expression would be:=IIf(DateDiff("d",Fields!Case_Start_Date.Value,Today()) > 30,"Red",IIf(DateDiff("d",Fields!Case_Start_Date.Value,Today()) >= 15 AND DateDiff("d",Fields!Case_Start_Date.Value,Today()) <= 30,"Yellow","White")))
Too many closing parentheses?
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
May 15, 2018 at 11:33 am
Jeffrey Williams 3188 - Tuesday, May 15, 2018 11:27 AMThom A - Monday, May 14, 2018 2:24 AMLike I said in my initial reply, as you only have 3 values, you only need 2 IIFs,. The number of IIFSs you need is effectively Number of possible Values - 1. So your expression would be:=IIf(DateDiff("d",Fields!Case_Start_Date.Value,Today()) > 30,"Red",IIf(DateDiff("d",Fields!Case_Start_Date.Value,Today()) >= 15 AND DateDiff("d",Fields!Case_Start_Date.Value,Today()) <= 30,"Yellow","White")))Too many closing parentheses?
Well spotted. 🙂
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
Viewing 10 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply