Like SQL

P

PeterM

I need to search a memo field and find all records with a specific date
somewhere in the comments...search_date below is defined as a "date"

Dim temp_search_date As String
temp_search_date = search_date
Set leads = dbCDMi.OpenRecordset _
("SELECT comments from leads_cdmi where comments like %" & temp_search_date
& "%", dbOpenForwardOnly)

why doesn't this work? I get an error message saying

3075
Syntax error in query expression 'comments like %11/14/2005%

thanks in advance!
 
V

Van T. Dinh

Try:

Set leads = dbCDMi.OpenRecordset _
("SELECT comments from leads_cdmi where comments like ""%" &
temp_search_date
& "%""", dbOpenForwardOnly)
 
P

PeterM

THANK YOU! THANK YOU! THANK YOU!

Van T. Dinh said:
Try:

Set leads = dbCDMi.OpenRecordset _
("SELECT comments from leads_cdmi where comments like ""%" &
temp_search_date
& "%""", dbOpenForwardOnly)
Click to expand...
 
J

John Vinson

I need to search a memo field and find all records with a specific date
somewhere in the comments...search_date below is defined as a "date"

Dim temp_search_date As String
temp_search_date = search_date
Set leads = dbCDMi.OpenRecordset _
("SELECT comments from leads_cdmi where comments like %" & temp_search_date
& "%", dbOpenForwardOnly)

why doesn't this work? I get an error message saying

3075
Syntax error in query expression 'comments like %11/14/2005%

thanks in advance!
Click to expand...

The wildcard character in JET databases is * rather than %; and you
need quotemarks around the string you're searching for:

"SELECT comments from leads_cdmi where comments like '*" &
temp_search_date & "*'", dbOpenForwardOnly

so it will parse to

comments like '*11/14/2005*'

Note that if you have variably formatted dates in your memo field
(e.g. sometimes 8/10/2005 and sometimes 08/10/2005) you may miss data.

John W. Vinson[MVP]
 
Top