[RESOLVED] trouble with LIKE operator

[daveinarmstrong]

i am new to the LIKE operator. i am setting up a search in an access database so the user can search a partial string, eg search for "White" and find all records with the word white as part of the field contents.
this works when i make a query inside access but i cannot duplicate it in VB6 when connected to the same database table. i get an empty recordset
what am i doing wrong?

   
Private Sub Form_Load()
Set CNSKUList = New ADODB.Connection
CNSKUList.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & WorkFlowDatabasePath & ";" & "user id=admin;password=;"
CNSKUList.Open
Set RstSKUList = New ADODB.Recordset
end sub

Private Sub SearchButton_Click()
 RstSKUList.Open "select * from SKULIST WHERE Description LIKE ' * White * '  ", CNbatchtable, adOpenKeyset, adLockOptimistic, adCmdTableDirect
               Set SKUListMSHFlexGrid.DataSource = RstSKUList
   RstSKUList.Close
end sub

[jggtz]

Try with no spaces

LIKE '*White*' ",

and, try with % instead of *

LIKE '%White%' ",

[daveinarmstrong]

Thanks, this works.
what if i was using a variable for the search string? i am getting no data using a variable.

dim MyVariable as string
MyVariable = "White"

LIKE '%MyVariable%' ",

[TysonLPrice]

"LIKE '%" & MyVariable & "%' "

[si_the_geek]

Just like other times you want to append a variable to a string, use the variable rather than a piece of text which happens to be the same as the variable name.

For more information, see the article How can I put the value of a variable/control into my SQL statement? from our Database Development FAQs/Tutorials (at the top of this forum)


edit: I was slow!

[daveinarmstrong]

this works, thanks.

LIKE '%" & MyVar & "%'