2 Replies Latest reply on Jun 8, 2012 12:30 PM by Serge GABRIEL

    Database error 0x80040E14: Expression too complex in query expression

    Serge GABRIEL

      Hello all!

       

      I'm reaching this message. Is there anything I can do to overcome it and get the calculations ?

       

      I think it’s because there are too many IF put together (more than 20), because when I delete some of them, it works!

       

      How can I do it a way it works?

       

      Thanks a lot in advance!

       

      Here is my calculated field:

       

       

      IF [Plan/Real/Baseline]="Planed"

      THEN

      IF [ID Action]="ID01"

      THEN Dateadd('month',7,[End])

      ELSE IF [ID Action]="ID02"

           THEN Dateadd('month',17,[End])

           ELSE IF [ID Action]="ID03"

      THEN Dateadd('month',17,[End])

      ELSE IF [ID Action]="ID04"

      THEN Dateadd('month',7,[End])

      ELSE IF [ID Action]="ID05"

      THEN Dateadd('month',6,[End])

      ELSE IF [ID Action]="ID06"

      THEN Dateadd('month',7,[End])

      ELSE IF [ID Action]="ID07"

      THEN Dateadd('month',6,[End])

      ELSE IF [ID Action]="ID08"

      THEN Dateadd('month',1,[End])

      ELSE IF [ID Action]="ID09"

      THEN Dateadd('month',3,[End])

      ELSE IF [ID Action]="ID10"

      THEN Dateadd('month',4,[End])

      ELSE IF [ID Action]="ID11"

      THEN Dateadd('month',4,[End])

      ELSE IF [ID Action]="ID12"

      THEN Dateadd('month',3,[End])

      ELSE IF [ID Action]="ID13"

      THEN Dateadd('month',9,[End])

      ELSE IF [ID Action]="ID14"

      THEN Dateadd('month',5,[End])

      ELSE IF [ID Action]="ID15"

      THEN Dateadd('month',7,[End])

      ELSE IF [ID Action]="ID16"

      THEN Dateadd('month',7,[End])

      ELSE IF [ID Action]="ID17"

      THEN Dateadd('month',7,[End])

      ELSE IF [ID Action]="ID18"

      THEN Dateadd('month',5,[End])

      ELSE IF [ID Action]="ID19"

      THEN Dateadd('month',5,[End])

      ELSE IF [ID Action]="ID20"

      THEN Dateadd('month',6,[End])

      END

      END

      END

      END

      END

      END

      END

      END

      END

      END

      END

      END

      END

      END

      END

      END

      END

      END

      END

      END

      END

        • 1. Re: Database error 0x80040E14: Expression too complex in query expression
          Matthew Hull

          Serge,

           

          Because of the use of [ID Action] in every "if then" action, the structure can be changed to a CASE function. Please try the following statement:

           

          IF [Plan/Real/Baseline]="Planed"

          THEN

          CASE [ID Action]

          WHEN "ID01"

          THEN Dateadd('month',7,[End])

          WHEN "ID02"

          THEN Dateadd('month',17,[End])

          WHEN "ID03"

          THEN Dateadd('month',17,[End])

          WHEN "ID04"

          THEN Dateadd('month',7,[End])

          WHEN "ID05"

          THEN Dateadd('month',6,[End])

          WHEN "ID06"

          THEN Dateadd('month',7,[End])

          WHEN "ID07"

          THEN Dateadd('month',6,[End])

          WHEN "ID08"

          THEN Dateadd('month',1,[End])

          WHEN "ID09"

          THEN Dateadd('month',3,[End])

          WHEN "ID10"

          THEN Dateadd('month',4,[End])

          WHEN "ID11"

          THEN Dateadd('month',4,[End])

          WHEN "ID12"

          THEN Dateadd('month',3,[End])

          WHEN "ID13"

          THEN Dateadd('month',9,[End])

          WHEN "ID14"

          THEN Dateadd('month',5,[End])

          WHEN "ID15"

          THEN Dateadd('month',7,[End])

          WHEN "ID16"

          THEN Dateadd('month',7,[End])

          WHEN "ID17"

          THEN Dateadd('month',7,[End])

          WHEN "ID18"

          THEN Dateadd('month',5,[End])

          WHEN "ID19"

          THEN Dateadd('month',5,[End])

          WHEN "ID20"

          THEN Dateadd('month',6,[End])

          END

          END

          • 2. Re: Database error 0x80040E14: Expression too complex in query expression
            Serge GABRIEL

            Thanks for your idea Matthew.

             

            I tried but the same issue remains...