SQL instr - string 2 is db

Hi, I am trying to do the following,

RS.Open "Select Field1 from Table1 WHERE Instr(1,'ZZhelloZZZ',Field1)", Connection, adOpenDynamic, adLockOptimistic

The difference is string two is the record in the database and hello is the user defined string. Works OK. A message box returns the string hello.

but what I need is,

RS.Open "Select Field1 from Table1 WHERE Instr(1,' " & StrVar & ',Field1)", Connection, adOpenDynamic, adLockOptimistic

It will not accept the variable which is the user defined string. What am I doing wrong.

Any ideas appreciated. Thanks

RS.Open "Select Field1 from Table1 WHERE Instr(1,' " & StrVar & ',Field1)", Connection, adOpenDynamic, adLockOptimistic

should this be perhaps :


RS.Open "Select Field1 from Table1 WHERE Instr(1,'" & StrVar & "',Field1)", Connection, adOpenDynamic, adLockOptimistic

should this be perhaps :


RS.Open "Select Field1 from Table1 WHERE Instr(1,'" & StrVar & "',Field1)", Connection, adOpenDynamic, adLockOptimistic


IT wasn't processing because a special character go in the way in the string. I used a function to remove all the special characters and just have alphanumeric and then it worked.