Coldfusion Syntax Error Using "Contains"

Here is the error (actual code down below error):

Error Executing Database Query.  
[Macromedia][SequeLink JDBC Driver][ODBC Socket][Microsoft][ODBC Microsoft Access Driver] Syntax error (missing operator) in query expression 'Contains(debt_sale.last_name, 'cannon') OR Contains(debt_sale.address_1, 'cannon') OR Contains(debt_sale.address_2, 'cannon') OR Contains(debt_sale.address_3, 'cannon')'.  
 
The error occurred in C:\Collections\Kaleidoscope\debt_sale\debt_sale_search.cfm: line 32
 
30 :       <cfelse>
31 :       WHERE Contains(debt_sale.address_2, '#strAddress#') OR Contains(debt_sale.address_1, '#strAddress#') OR Contains(debt_sale.address_3, '#strAddress#');      
32 : </cfif>
33 : </cfquery>
34 :

--------------------------------------------------------------------------------
 
SQL    SELECT debt_sale.acct_no, debt_sale.first_name, debt_sale.last_name, debt_sale.ssn, debt_sale.address_1, debt_sale.address_2, debt_sale.address_3, debt_sale.city, debt_sale.state, debt_sale.zipcode, debt_sale.home_phone, debt_sale.business_phone, debt_sale.disconnect_date, debt_sale.total_bal, debt_sale.principal_amt, debt_sale.charge_off_date, debt_sale.last_pay_amt, debt_sale.last_pay_date, debt_sale.acct_open_date, debt_sale.cell_no, debt_sale.comments, debt_sale.reviewed_by, debt_sale.date_reviewed FROM debt_sale WHERE Contains(debt_sale.last_name, 'cannon') OR Contains(debt_sale.address_1, 'cannon') OR Contains(debt_sale.address_2, 'cannon') OR Contains(debt_sale.address_3, 'cannon');  
DATASOURCE   DebtSale
 
Here is the actual code:

<cfquery name="qryDebtSale" datasource="DebtSale">
SELECT debt_sale.acct_no, debt_sale.first_name, debt_sale.last_name, debt_sale.ssn, debt_sale.address_1, debt_sale.address_2, debt_sale.address_3, debt_sale.city, debt_sale.state, debt_sale.zipcode, debt_sale.home_phone, debt_sale.business_phone, debt_sale.disconnect_date, debt_sale.total_bal, debt_sale.principal_amt, debt_sale.charge_off_date, debt_sale.last_pay_amt, debt_sale.last_pay_date, debt_sale.acct_open_date, debt_sale.cell_no, debt_sale.comments, debt_sale.reviewed_by, debt_sale.date_reviewed
FROM debt_sale
<cfif strAcctNo neq ''>
      WHERE debt_sale.acct_no = '#strAcctNo#');
      <cfelseif strLastName neq ''>
      WHERE Contains(debt_sale.last_name, '#strLastName#') OR Contains(debt_sale.address_1, '#strLastName#') OR Contains(debt_sale.address_2, '#strLastName#') OR Contains(debt_sale.address_3, '#strLastName#');
      <cfelseif strSSN neq ''>
      WHERE debt_sale.ssn = '#strSSN#';
      <cfelse>
      WHERE Contains(debt_sale.address_2, '#strAddress#') OR Contains(debt_sale.address_1, '#strAddress#') OR Contains(debt_sale.address_3, '#strAddress#');      
</cfif>
</cfquery>
makilaAsked:
Who is Participating?
 
sigmaconConnect With a Mentor Commented:
I am assuming you're trying to write a query that selects rows based on whether a string (such as an address) can be found within the value of a column in a row. The SQL function CONTAINS is only applicable to FULL-TEXT INDEXED COLUMNS ON SQL SERVER. You are talking to a MS Access database (according to the error output). AFAIK, Access does not have this function (at least not the Access 2000 that I just checked. You may want to try

WHERE
  debt_sale.address_2 like '%#strAddress#%'
  OR debt_sale.address_1 like '%#strAddress#%'
  OR debt_sale.address_3 like '%#strAddress#%'

and if that blows, give the old JET-style SQL a try

WHERE
  debt_sale.address_2 like '*#strAddress#*'
  OR debt_sale.address_1 like '*#strAddress#*'
  OR debt_sale.address_3 like '*#strAddress#*'
0
 
pinaldaveCommented:
i do nto know how you can use contains you should have used IN

llike
where debt_sale.address_2 in ('#strAddress#')

something like that...
0
 
makilaAuthor Commented:
Thanks! Using the wildcards worked like a charm. Have a great weekend :)
0
All Courses

From novice to tech pro — start learning today.