4 Replies Latest reply on Apr 19, 2018 7:29 PM by Hari Ankem

    Custom SQL. Data type mismatch in criteria expression.

    Alexandre PAREJA-GARCIA
    Alexandre PAREJA-GARCIA Apr 12, 2018 10:48 PM

      Hi,

      I use Tableau 10.3

      I wrote the below script for a custom SQL from 3 different .mdb tables.

      I get the message error: Microsoft JET database error 0x80040E07: Data type mismatch in criteria expression.

       

      I simplified the number of fields with the below script and get the same error:

      Select

          [Account]

          ,[Contract Number]

          ,Null as [Estimated Amount (SUM)]

       

      From

          [VIEW_ACCOUNT_BOOKINGS_REVENUE].[VIEW_ACCOUNT_BOOKINGS_REVENUE]

       

      Union

         

      Select

          [Account]

          ,Null as [Contract Number]

          ,[Estimated Amount (SUM)]

       

      From

          [VIEW_ACCOUNT_CONTRACT].[VIEW_ACCOUNT_CONTRACT]

       

      Union

       

      Select

          Null as [Account]

          ,Null as [Contract Number]

          ,Null as[Estimated Amount (SUM)]

       

      From

          [VIEW_ACCOUNT_HOURS_COST].[VIEW_ACCOUNT_HOURS_COST]

       

      I get the message error: Microsoft JET database error 0x80040E07: Data type mismatch in criteria expression.

       

      I tried to remove one of the union and did:

       

      Red Union Green     

      Red union Blue

      Green Union Blue

       

      All the different combinations work except when I tried with 3 Union at a time.

       

      As anyone encounter this issue before?

       

      Regards,

       

      Alex

        • 1. Re: Custom SQL. Data type mismatch in criteria expression.
          Hari Ankem
          Hari Ankem Apr 12, 2018 11:10 PM (in response to Alexandre PAREJA-GARCIA)

          Since there are nulls for the column values, you should type cast them so that a column has the same data type across the unions.

           

          See if this works:

           

          Select

             Cstr( [Account]) AS Account

              ,Cstr([Contract Number]) AS Contract_Number

              ,Cdbl(Null) as Estimated_Amount

           

          From

              [VIEW_ACCOUNT_BOOKINGS_REVENUE].[VIEW_ACCOUNT_BOOKINGS_REVENUE]

           

          Union

            

          Select

             Cstr( [Account]) AS Account

              ,Cstr(Null) AS Contract_Number

              ,Cdbl([Estimated Amount (SUM)]) as Estimated_Amount

           

          From

              [VIEW_ACCOUNT_CONTRACT].[VIEW_ACCOUNT_CONTRACT]

           

          Union

           

          Select

             Cstr(Null) AS Account

              ,Cdbl(Null) as Estimated_Amount

           

          From

              [VIEW_ACCOUNT_HOURS_COST].[VIEW_ACCOUNT_HOURS_COST]

          • 2. Re: Custom SQL. Data type mismatch in criteria expression.
            Alexandre PAREJA-GARCIA
            Alexandre PAREJA-GARCIA Apr 12, 2018 11:41 PM (in response to Hari Ankem)

            Hi Hari,

             

            Thanks for your reply. Unfortunately it is not working as I get the error: "Microsoft JET database error 0x80040E14: Invalid use of Null"

            I tried to remove the cstr from the null and then get the previous error message: Microsoft JET database error 0x80040E07: Data type mismatch in criteria expression.

            • 3. Re: Custom SQL. Data type mismatch in criteria expression.
              Alexandre PAREJA-GARCIA
              Alexandre PAREJA-GARCIA Apr 19, 2018 7:02 PM (in response to Alexandre PAREJA-GARCIA)

              Hi,

               

              Anyone have been running through the same issue?

               

              Regards,

               

              Alex

              • 4. Re: Custom SQL. Data type mismatch in criteria expression.
                Hari Ankem
                Hari Ankem Apr 19, 2018 7:29 PM (in response to Alexandre PAREJA-GARCIA)

                Am sorry, I missed a column in the third union. Also, made a change to have zeroes instead of null for the amount. Can you please try this?

                 

                Select

                   Cstr( [Account]) AS Account

                    ,Cstr([Contract Number]) AS Contract_Number

                    ,0 as Estimated_Amount

                 

                From

                    [VIEW_ACCOUNT_BOOKINGS_REVENUE].[VIEW_ACCOUNT_BOOKINGS_REVENUE]

                 

                Union

                  

                Select

                   Cstr( [Account]) AS Account

                    ,Cstr(Null) AS Contract_Number

                    ,Nz([Estimated Amount (SUM)],0) as Estimated_Amount

                 

                From

                    [VIEW_ACCOUNT_CONTRACT].[VIEW_ACCOUNT_CONTRACT]

                 

                Union

                 

                Select

                   Cstr(Null) AS Account

                    ,Cstr(Null) AS Contract_Number

                    ,0 as Estimated_Amount

                 

                From

                    [VIEW_ACCOUNT_HOURS_COST].[VIEW_ACCOUNT_HOURS_COST]