6 Replies Latest reply on Apr 21, 2016 2:37 PM by Brent Mathison

    Issue with IF Statement using Null Date Logic

    Jerry Ward

      I understand you cannot use IFNULL on dates but how would I calculate the following IF statement on a Null Date?

       

      IF IFNULL(STR([Due Date]), " ") THEN [Normal Past Due]

      ELSEIF [Due Date] < ([Date] + 1) THEN ([Date] + 1) - [Due Date]

      ELSE [Due Date]

      END

       

      I get the error: Expected type Boolean, found string.  Comparison in 'IF' expression must be Boolean type.

        • 1. Re: Issue with IF Statement using Null Date Logic
          Dan Sanchez

          Hi Jerry!

           

          What about using ISNULL()?

           

          IF ISNULL([Due Date]) THEN [Normal Past Due]

          ELSEIF [Due Date] < ([Date] + 1) THEN ([Date] + 1) - [Due Date]

          ELSE [Due Date]

          END

          • 2. Re: Issue with IF Statement using Null Date Logic
            John Sobczak

            Dan is right - but you may need to wrap a STR around due date as I seem to recall date fields don't work consistent with Nulls.  The reason you get the error is because IFNULL is not a Boolean (ie. it doesn't return true/false), but rather it returns a string value.

            • 3. Re: Issue with IF Statement using Null Date Logic
              Jerry Ward

              I used Dan's equation and I am getting the error "Expected type float, found date. Results types 'IF' expressions must match"

              I also tried John's STR() around Due Date and I still get the same error...Any ideas?

               

              Thanks Guys?

              • 4. Re: Issue with IF Statement using Null Date Logic
                Brent Mathison

                Hi Jerry. What if you changed the first line of your original formula to this:

                 

                IF IFNULL(STR([Due Date]), " ") = " " THEN [Normal Past Due]

                 

                 

                Edit: You may also need to edit the second part of your formula.In the IF and ELSE portions, you are returning dates. In the ELSEIF portion, it appears that you are returning a number. What date are you trying to return with that calculation?

                • 5. Re: Issue with IF Statement using Null Date Logic
                  Jerry Ward

                  I adjusted it a little.  Overall I am trying to do the following.

                  If the [Due Date (Date Field)] is Null or Blank then take [Normal Past Due (Number)]

                  Otherwise if  [Due Date (Date Field)] < [Date+1 (Date Field)] THEN

                  Give me the Days between [Date+1 (Date Field)] and [Due Date (Date Field)]

                  Otherwise just give me [Due Date (Date Field)]

                   

                  IF ISNULL(STR([Due Date]), " ") = " "  THEN [Normal Past Due]

                  ELSEIF [Due Date] < [Date +1] THEN DATEDIFF('day',[Date +1],[Due Date])

                  ELSE [Due Date]

                  END

                   

                  I get an error : "Expected type integer, found date.  Results types from 'IF' expressions must match."

                  • 6. Re: Issue with IF Statement using Null Date Logic
                    Brent Mathison

                    Ah...yes...all of your IF statement conditions must return the same data type. As you have it now, you are telling it

                     

                    IF x THEN return number

                    ELSEIF y THEN return number

                    ELSE return date

                     

                    You will need to return all numbers or all dates. Could you break this into 2 columns? Column 1 would be the results of your IF/ELSEIF. And column 2 would display the Due Date if Column 1 is not populated. Example:

                     

                    Column 1:

                    IF ISNULL(STR([Due Date]), " ") = " "  THEN [Normal Past Due]

                    ELSEIF [Due Date] < [Date +1] THEN DATEDIFF('day',[Date +1],[Due Date])

                    END

                    (this will return NULL if neither condition is met)

                     

                    Column 2:

                    IF ISNULL([Column 1]) THEN [Due Date] END

                    (this will return NULL if Column 1 is populated.

                     

                     

                    Another option is to return string values in your IF statement:

                    IF ISNULL(STR([Due Date]), " ") = " "  THEN STR([Normal Past Due] )

                    ELSEIF [Due Date] < [Date +1] THEN STR(DATEDIFF('day',[Date +1],[Due Date]))

                    ELSE STR([Due Date]) //I'm fairly certain you can convert dates to strings. If not, you can parse the day, month, and year separately and convert each to a string

                    END

                     

                    This ensures all of your returned values are the same data type.