What is wrong with this WHERE statement?

[Guest]

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!!!!!

[-__cfSearching__-]

What is the problem with this WHERE statement?

What is the full error message ?

[Guest]

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__- · Accepted solution]

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?

[Guest]

I'm guessing that the CreateODBCDate() isn't producing that output because the template hasn't processed yet. You were right about the EQ, saved me from pulling out the rest of my hair. Thank you! Again!

[-__cfSearching__-]

emartek1 wrote:

I'm guessing that the CreateODBCDate() isn't producing that output because the template hasn't processed yet. You were right about the EQ, saved me from pulling out the rest of my hair. Thank you! Again!

Actually it has or you would not be seeing a database driver error.  But I am glad it is working now!