3 Replies Latest reply on Nov 6, 2009 5:11 AM by -==cfSearching==-

    Data type mismatch in criteria expression. What am I not seeing??

    emartek1

      Once again I'm stuck! I have included the query I'm trying to run below.  I have also indicated the data types of my fields.

       

       

      <cfquery name="qGetOrders" datasource="manna_premier">
      SELECT DISTINCT Count(ID) AS CountOfID,   - number
                                   TMName,   -text
                                   Sum(Quantity) AS SumOfQuantity,   - number
                                   Count(NewExisting) AS CountOfNewExisting -number
      FROM Users, Orders, ProductOrders
      WHERE SaleDate BETWEEN #CreateODBCDate(FORM.Start)# AND #CreateODBCDate(FORM.End)#
      GROUP BY UserZone, TMName
      </cfquery>

       

      When run it returns this error message....

       

       

       

      Error Executing Database Query.

      [Macromedia][SequeLink JDBC Driver][ODBC Socket][Microsoft][ODBC Microsoft Access Driver] Data type mismatch in criteria expression.
      The error occurred in D:\Inetpub\mannapremier\zvp_report2.cfm: line 11
      9 :                     Count(NewExisting) AS CountOfNewExisting
      10 : FROM Users, Orders, ProductOrders
      11 : WHERE SaleDate BETWEEN #CreateODBCDate(FORM.Start)# AND #CreateODBCDate(FORM.End)#
      12 : GROUP BY UserZone, TMName
      13 : </cfquery>
      

      SQLSTATE  22018
      SQL   SELECT DISTINCT Count(ID) AS CountOfID, TMName, Sum(Quantity) AS SumOfQuantity, Count(NewExisting) AS CountOfNewExisting FROM Users, Orders, ProductOrders WHERE SaleDate BETWEEN {d '2009-10-01'} AND {d '2009-10-31'} GROUP BY UserZone, TMName
      VENDORERRORCODE  -3030
      DATASOURCE  manna_premier


      I use the where clause on several other pages and it works. I don't understand where my data type is mismatched.

      Can anyone point me in the right direction?