ADO Like Operator

[gdebacker]

I'm progamming in VB6 with a reference ADO 2.5 and an Access 2 db file.

I have a Keywords table. As a user enters a new record the two
memo fields are scanned for keywords and the Keywords table is
populated with a list keywords for that record.

I then have a search utility that allows users to search on the
keyword table. If I create a query using equals (=) sign for
an exact match the query executes and the correct number of
records are returned. However, when I use the Like
operator with astrisks, using the same keyword or a shortened
varaition, I get no records returned.

sKeyWord = "blue"
'This first one returns 5 records
sSQL = "SELECT * FROM Keywords WHERE Keyword = '" & sKeyWord & "'"
 
'This one returns 0
sSQL = "SELECT * FROM Keywords WHERE Keyword Like '*" & sKeyWord & "*'"
 
I tried this
objRS.Open sSQL, objConn, adOpenKeyset, adCmdTable
 
And this
objRS.Open sSQL, objConn, adOpenKeyset, adLockOptimistic
 
And This
objRS.Open sSQL, objConn, adOpenStatic, adCmdTable

I tried similar queries on the Memo fields using the Like operator
and returned 0 records. When I set up a query in Access using
Like I get the correct results. The only difference in the queries
was that Access is using more brackets and appended the table
name to the field name (e.g. [Keywords.Keyword]) I tried this in
my program I returned 0 records.

What am I doing wrong?

Greg

[gdebacker]

Boy Google.com is amazing. I went there and typed in ado like
operator and this was the first page it returned...

http://www.learnasp.com/learn/FAQdbLIKE.asp

The first sentence at the top of that page answered my question.

Unfortunately I then went back to the MSDN CD to see if I was
going crazy and found out, to my own embarrassment, I just did
not finish reading the paragraph.

From MSDN

Value is the value with which you will compare the field values (for example, 'Smith', #8/24/95#, 12.345 or $50.00). Use single quotes with strings and pound signs (#) with dates. For numbers, you can use decimal points, dollar signs, and scientific notation. If Operator is LIKE, Value can use wildcards. Only the asterisk (*) and percent sign (%) wild cards are allowed, and they must be the last character in the string. Value cannot be Null.

I saw the asterisk (*), and because I had used it so much in
Access and VB3 I went with it. There is an hour and a half of my
life I'll never get back.

Greg