5 Replies Latest reply on Jun 11, 2012 3:08 AM by Serge GABRIEL

    Database error 0x80040E14: Expression too complex in query expression

    Karine Jouffret

      Hi,

       

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

       

      Thanks in advance

       

      Karine

        • 1. Re: Database error 0x80040E14: Expression too complex in query expression
          Tracy Rodgers

          Hi Karine,

           

          Would you be able to post the workbook (saved as a twbx file) so we can take a closer look?

           

          If not, perhaps provide us with the calculation and some background about it/screen shots?

           

          Thanks!

           

          -Tracy

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

            Hello!

             

            I have the same problem…

            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

            • 3. 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

              • 4. Re: Database error 0x80040E14: Expression too complex in query expression
                Russell Christopher

                If your data source is Excel, a Text File, or Microsoft Access, try this:

                 

                • Temporarily remove this calculation from the workbook
                • Create a extract against the data source
                • Replace the calculation.

                 

                Your problem could be that the resulting query from your expression is simply too complex for the JET database engine to handle.

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

                  Hello Russel,

                   

                  Thanks for your reply !

                  I tried again with the "CASE" function and it worked... I don't know why it didn't work before.

                   

                  Best Regards,

                  Serge