Skip to main content
A
Anonymous
November 6, 2009
Question

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

  • November 6, 2009
  • 2 replies
  • 2830 views

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?

This topic has been closed for replies.

2 replies

D
Dan_Bracuk
Inspiring
November 6, 2009

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.

BKBK
Community Expert
BKBKCommunity Expert
Community Expert
November 6, 2009

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__-
Inspiring
November 6, 2009

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

Terms & ConditionsAccessibility statement
Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices