In my query I want to search a certain field in the database. The field is of memo type. I read that the Where Contains clause will only work on an SQL database..so I am using Access and am wondering if there is some type of equivalent or workaround for accomplishing this.

The input for the search criteria is a simple form input box, sorta like a keyword search. I just want to return the records with that "keyword" in the specified memo field...or for that matter, it could just be the whole table..either or....

Thanks

You can accomplish the same thing by using the 'Like' keyword in Access. For example if you wanted to search a field, but didn't want to be restricted to results that only fit the search string you would write the where statement like this: WHERE (((Customers.FirstName) Like "*substring*"));

With this sytax, the query would return all fields that contain the word substring, where ever they may be in the field.

Thanks a lot jray! I got it to work perfectly!

I have a similar issue with an Access query except that the database field has a comma separted list of words. I want to pass a url variable to the query and pull all records where the url variable (one word) is found within that list. I have tried WHERE 'comma_list LIKE url.variable but it returns no results.

Here is my basic query:

<cfquery name="getResults" datasource="db_name">
SELECT * FROM table_name
WHERE
Deleted = 0
AND
category_list LIKE 'url.variable'
ORDER BY name
</cfquery>

I want th record to be pulled if the url variable is found anywhere in the list stored in the database field.

Any suggestions?

Thanks!

Use this:
WHERE category_list LIKE '%url.variable%'

The percent signs match anything. I'm assuming you mean to insert the value of url.variable here, not the actual word "url.variable". Putting the percents before and after will match [anything] + [url.variable] + [anything].