Skip to main content
A
Anonymous
October 18, 2009
Answered

What is wrong with this WHERE statement?

  • October 18, 2009
  • 1 reply
  • 750 views

What is the problem with this WHERE statement?

<cfquery datasource="manna_premier" name="kit_report">
SELECT SaleDate,
       TerritoryManager,
    Distributor,
    DealerID,
    Variable,
    US_Dealers.ID,
       DealerName,
    DealerAddress,
    DealerCity,
    DealerState,
    DealerZIPCode   
FROM Orders, US_Dealers
WHERE US_Dealers.ID EQ DealerID AND SaleDate BETWEEN #CreateODBCDate(FORM.Start)# AND #CreateODBCDate(FORM.End)#
ORDER BY SaleDate
</cfquery>

This is driving me nuts!!!!!

    This topic has been closed for replies.
    Correct answer -__cfSearching__-

    Syntax error (missing operator) in query expression 'US_Dealers.ID EQ DealerID

    You are accidentally using the CF equals operator:  "EQ"

    Instead of the one for MS Access:   "="

    [Macromedia][SequeLink JDBC Driver][ODBC Socket][Microsoft][ODBC Microsoft Access Driver] ...

    in query expression ... AND SaleDate BETWEEN #10/1/2009# AND #10/18/2009#'.

    I do not see how CreateODBCDate() would produce those values.  But perhaps it is just a quirk of the error message?

    1 reply

    -
    -__cfSearching__-
    Inspiring
    October 18, 2009

    What is the problem with this WHERE statement?

    What is the full error message ?

    A
    Anonymous
    October 18, 2009

    Error Executing Database Query.

    [Macromedia][SequeLink JDBC Driver][ODBC Socket][Microsoft][ODBC Microsoft Access Driver] Syntax error (missing operator) in query expression 'US_Dealers.ID EQ DealerID AND SaleDate BETWEEN #10/1/2009# AND #10/18/2009#'.
    The error occurred in D:\Inetpub\mannapremier\kit_report2.cfm: line 15
    13 :         DealerZIPCode        
14 : FROM Orders, US_Dealers
15 : WHERE US_Dealers.ID EQ DealerID AND SaleDate BETWEEN #CreateODBCDate(FORM.Start)# AND #CreateODBCDate(FORM.End)#
16 : ORDER BY SaleDate
17 : </cfquery>
    -
    -__cfSearching__-Correct answer
    Inspiring
    October 18, 2009

    Syntax error (missing operator) in query expression 'US_Dealers.ID EQ DealerID

    You are accidentally using the CF equals operator:  "EQ"

    Instead of the one for MS Access:   "="

    [Macromedia][SequeLink JDBC Driver][ODBC Socket][Microsoft][ODBC Microsoft Access Driver] ...

    in query expression ... AND SaleDate BETWEEN #10/1/2009# AND #10/18/2009#'.

    I do not see how CreateODBCDate() would produce those values.  But perhaps it is just a quirk of the error message?

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