1

I need a help on the below Expression, I've

column A - Date, 

Column B-  Yes/No , 

Column C - Date.

Condition

If Column B = Yes and Column A has a Date then text box in *Grey

If Column A don't have Date, then check if Column C has date

then if Column C date > Today and Column B = No text box Green

If Column C date < Today and Column B = No text box Red

Also if there are no entries in any of the column then No color,

I tried to put ISNOTHING(For all 3 columns) with And Operator before switch statement

user2086122
  • 65
  • 4
  • 10
  • I've observed that you have edited your question and that's why I've also updated my answer. Can you please try out with my answer? Hope it would be helpful to you – Pedram Dec 18 '15 at 10:02
  • I tried it, since Column B is the Checkbox value and it returns Bit 0/1 hence i just changed = 1 instead of yes or no. But the situation is when i dont have a date in column A or column C and Column B is unchecked , also Column C date has to change the color based on Today date and Column b has to be unchecked. – user2086122 Dec 18 '15 at 10:23

2 Answers2

0

Add your conditions as an expression in the font/color property of the required cell.

=SWITCH(
    Fields![Column B].Value = "Yes" AND IsNothing(Fields![Column A].Value), "White",
    IsNothing(Fields![Column A].Value) and Fields![Column C].Value > Today() and Fields![Column B].Value = "", "Red",
    Fields![Column C].Value > Today() and Fields![Column B].Value = "Yes", "Gray",
    Fields![Column C].Value < Today(), "Green"
    )
Shep
  • 638
  • 3
  • 15
0

First of all, it could not be the same what I'm providing you but you should try this,

=
SWITCH
(
    CStr(Fields!ColumnB.Value) = "Yes" AND NOT(IsNothing(CDate(Fields!ColumnA.Value))), "Grey",
    IIF(IsNothing(Fields!ColumnA.Value),IIF(NOT(IsNothing(Fields!ColumnC.Value)),IIF(CDate(Fields!ColumnC.Value) > Today() AND Fields!ColumnB.Value="","")),"Green")
    CDate(Fields!ColumnC.Value) > Today() AND Fields!ColumnB.Value="Yes","Red"
    IsNothing(Fields!ColumnA.Value) AND IsNothing(Fields!ColumnB.Value) AND IsNothing(Fields!ColumnC.Value), ""
)

Also, if above doesn't work then try using separated IIF Statements. It would be more easy.

Pedram
  • 6,256
  • 10
  • 65
  • 87
  • I tried it, since Column B is the Checkbox value and it returns Bit 0/1 hence i just changed = 1 instead of yes or no. But the situation is when i dont have a date in column A or column C and Column B is unchecked , also Column C date has to change the color based on Today date and Column b has to be unchecked – user2086122 Dec 18 '15 at 10:31
  • sorry I'm not getting you, you have mentioned in above comment that `But the situation is when i don't have a date in column A or column C and Column B is unchecked ` - so in this case what should be the color? can you please elaborate... – Pedram Dec 18 '15 at 11:01
  • I have these cases , If column A and Column B and Column c do not have any value - No color , If there is a date in Column A and if Column B is Checked = "Grey color", If column A do not have any value , we check if Column C has date, if there is a date we check whether that date is > Today ="Green", if column c date < Today ="Red", so when we check for column C , column B has to be unchecked. – user2086122 Dec 18 '15 at 17:44