SQL Select with Like operator and variable obtained from a search problem

I have searched a recordset using a text box and search criteria. It succesfully lists the matching feild values from the recordset into a list box. Then I want to select one of the field values from the listbox and set a global variable gstrCompanySelected to the field value then use that varable in a select statement after a like operator to set a grstCurrentRS object variable


Set grstCurrentRS = gdbCurrent.OpenRecordset("Select * from tblCustomers where fldCompanyName like " & "'" & gstrCompanySelected & "'")

the varaible gstrCompanySelected was set via a listbox in the form frmFind
MS_OS.gstrCompanySelected = lstSelected

lstSelected is the list box containing the field values from a recordset search

So after opening a database and searching a specific field using a textbox as search Input it lists all matches in the list box.. so far so good but I have tried several versions of the above code without luck.

[Clunietp]

I'm not sure what your question is, but your SQL LIKE statemtent is missing the wildcard character:

change this:
("Select * from tblCustomers where fldCompanyName like " & "'" & gstrCompanySelected & "'"

to this:
("Select * from tblCustomers where fldCompanyName like " & "'*" & gstrCompanySelected & "*'"

I thought I tried it that way but Ill try it again.
btw I tried to just access the slelected item in the listbox directly instead of storing that value in a varible first and it works so Im sure its just I didnt have the exact syntax (tried at least 7 diff variations) but this works

Set grstCurrentRS = gdbCurrent.OpenRecordset("Select * from tblCustomers where fldCompanyName like '*" & frmFind.lstCompanies.Text & "*'")

Ill try your syntax to see if it works incase I want to use varibles some other time.

yep you got it Clunietp that also works cool cause Im gonna use the variable method later thx