SQL statement LIKE will not work with ' within string

[HebieBug]

Have an SQL statement that returns the relevant details based apon a LIKE statement.. This works fine at the moment until you get to the point where you run an LIKE with a ' in it..
This SQL statement searches through the field that holds the clients last names.. The problem occours if you enter, for example, a name like O'Connor.
User_Details is the table
LastName is the field
TxtLastL is the text box

strSql = "Select * from User_Details2 WHERE LastName LIKE '" & Me.TxtLastL & "%'"

You might be wondering why % and * is not.. Was helped out by a good dude on the net who told me that he also had the same problem and it worked for him and sure enough it worked.
Does anyone know of a way of running this SQL statement to accept ' in the Text box
Thanx

 

[jmarler]

HebieBug,
You need to change any single apostrophe &quot;'&quot; into 2 apostrophes &quot;''&quot; (this is not a quote - '' <> &quot. Use this line of code before running the SQL.


strData = replace$(strData,&quot;'&quot;,&quot;''&quot



- Jeff Marler B-)

 

[TimLarkin]

jmarler is right, however if the text has more than one ' in it, sql will ignore the first but not the second. instead of the replace function, enter the string as a literal in your sql string. The only problem that occurs is if your text has a # in it then your sol.
ex: SQL = &quot;Select * From MyTable where LName like '#&quot; & YourText & &quot;#%'&quot; (it's been awhile since I've used this, but I think thats the correct syntax.)

 

[chiph]

If you use an ADO Command object along with parameters, ADO will take care of this for you.
[tt]
sSQL = &quot;SELECT U.UserID AS UUserID, V.UserID AS VUserID &quot;
sSQL = sSQL & &quot;FROM tbl_User U, tbl_User V &quot;
sSQL = sSQL & &quot;WHERE U.UserRefCode=? and V.UserRefCode=?&quot;
adoComm.CommandText = sSQL

adoComm.Parameters.Append adoComm.CreateParameter(&quot;U.UserRefCode&quot;, adVarChar, adParamInput, 50, Left$(m_RptStructInfo.sManagerUserRefCode, 50))
adoComm.Parameters.Append adoComm.CreateParameter(&quot;V.UserRefCode&quot;, adVarChar, adParamInput, 50, Left$(m_RptStructInfo.sEmployeeUserRefCode, 50))

Set adoRS = New ADODB.Recordset
Set adoRS = adoComm.Execute()
[/tt]

Hope this helps

Chip H.