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

[Guest]

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?

[BKBK]

Look into the following:
1) The columns that appear in the group-by clause(UserZone, TMName) must also appear in the select-clause
2) Is the datatype of the column SaleDate actually a date?
3) Do you really need to select from the 3 tables Users, Orders, ProductOrders? It is apparent to me you don't. If not, then select from just the table(s) in which the columns occur.
4) If you indeed need to select from the 3 tables Users, Orders, ProductOrders, then you should qualify the column names like this, Users.ID, productOrders.Quantity, and so on.

[-__cfSearching__-]

4) If you indeed need to select from the 3 tables Users,

Orders, ProductOrders, then you should qualify the columns

names like this, Users.ID, productOrders.Quantity, and so

on.

You also need to join on the related columns. If you forget to do that the resultset will be a massive cartesian table (huge number of records):

1. rows in Table1 * #rows in Table2 * #rows in Table3

[Dan_Bracuk]

In addition to what's been said so far, this:

#CreateODBCDate(FORM.Start)#

will cause problems if it's not already causing problems.

First, use parsedatetime() to convert the strings from your form fields to proper date objects.

Second, use cfqueryparam.  It will help.