CONTAINS keyword in SQL for MS Access

I am trying to set up a word search for my website. I want to take what the user typed in and find any rows in my table that contain the word or pharse in any of the columns. The keyword CONTAINS would work perfectly but I am thinking it is not supported in Access (I'm using Access 2000 if that helps). My where clause looks like this:

...WHERE CONTAINS([Item Name],'Glass')

but I get this:

System.Data.OleDb.OleDbException: Syntax error (missing operator) in query expression 'CONTAINS( [Item Title] ,'Glass')'.

 

Use the "Like" keyword, as in-
...WHERE ([Item Name] Like "*Glass*")

Notice the asterisks preceding and following "Glass" which will find "Glass" at the beginning, end, or in the middle of the [Item Name] field.

 

First off... Thank You! I tried with the asterisks and it wasn't working. I tried it several different ways... * only in the front... then only in the back... with the () and without them... still nothing.

I then found this link on the LIKE keyword:http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnacc2k/html/acfundsql.asp

and noticed I could use % instead of * and that worked perfectly. Again, thanks for help it was much appreciated!

Mike
***

quote:Originally posted by Scott

Use the "Like" keyword, as in-
...WHERE ([Item Name] Like "*Glass*")

Notice the asterisks preceding and following "Glass" which will find "Glass" at the beginning, end, or in the middle of the [Item Name] field.

</blockquote id="quote"></font id="quote">

 

I'm glad you found the solution despite my slightly off-base reply.

This can be confusing since the wild card you use ("*" or "%") depends on where it is used. If you are writing a query in Access, you have to use the asterisk--the percent sign will not work. However if you are using OleDb to pass the query to Access, the reverse is true.