searching with sql using variables..

Hello, from reading some posts earlier I came up with this code to use sql to retrieve records. Please tell me what I am doing wrong.

rs.open "Select * From Table1 Where Number = '%" & MyVar1 & "%", ConnectString, adOpenStatic, adLockOptimistic

The MyVar1 is a is a numeric value since the Number field is a numeric value. It does not work however, can someone please help.

Thanks,
Thai

It's hard to say based purely on what you have shown, but I presume you are opening a connection before you attempt to open the recordset. Other than that I see no need for the percentage signs. If that doesn't try using an error handler to trap the specific error you are getting.

Try:

rs.open "Select * From Table1 Where Number = " & MyVar1, ConnectString, adOpenStatic, adLockOptimistic


Pattern matching isn't used with the equal operator ('='), it's used with the like operator ('like'). The other thing wrong with your statement is you have a single quote that has no matching single quote.

that worked, thanks :)