How to get the following SRSS Iff Expression Between Two Dates To Work

Question

I am struggling to get the below expression working; can anybody advise? The expression will run but all fields are filled with the #Error message.

=iif(Fields!From.Value>="01/09/2021" and Fields!From.Value <= "31/08/2022", "Sept 2021",
 iif(Fields!From.Value>= "01/09/2020" and Fields!From.Value <= "31/08/2021", "Sept 2020", 
 iif(Fields!From.Value >= "01/09/2019" and Fields!From.Value <= "31/08/2020", "Sept 2019", 
 iif(Fields!From.Value >= "01/09/2018" and Fields!From.Value <= "31/08/2019", "Sept 2018",
 iif(Fields!From.Value >= "01/09/2017" and Fields!From.Value <= "31/08/2018","Sept 2017",
 iif(Fields!From.Value >= "01/09/2016" and Fields!From.Value <="31/08/2017","Sept 2016","0"))))))

Any help will be much appreciated!

Answer 1

Assuming From is actually a date and not string then you could simplify if like this.

="Sept " &
    (YEAR(Fields!From.Value) 
        - IIF(Month(Fields!From.Value) >= 9, 0, 1)
        )

This just checks if the month >=9 and then subtracts either a zero or a 1 to the Year part of the From field. Then just stick "Sept " in front of it. This also means you don't need a long IIF or SWITCH statement, it will work for any year.

With regards to your original expression. If you need to use string literals to represent dates then it's always better to use non-ambiguous formats. "01/09/2021" can be interpreted as "1st September 2021" in the UK or as "9th January 2021" in the US. If you use "2021-09-01" this should always be interpreted correctly.