Like SQL

[Guest]

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!

 

[Van T. Dinh]

Try:

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

 

[Guest]

THANK YOU! THANK YOU! THANK YOU!

Try:

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

 

[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!

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]