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?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
sigmaconCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
makilaAuthor Commented:
Thanks! Using the wildcards worked like a charm. Have a great weekend :)
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Web Servers

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.