Quotes problem

When searching for a particular Employee I would use a SQL string as such .....

Code:

---

strSQL = "SELECT * FROM Employees " & _
        "WHERE Surname = '" & strSurname & "'"

---

What do I do if the strSurname has a single quote in it?

eg....

Code:

---

strSQL = "SELECT * FROM Employees " & _
        "WHERE Surname = 'O'Sullivan'"

---

Any help would be appreciated. :)

One way to get round the problem is to either place a extra ' character in the surname if you are using a literal string like so

Where surname= 'O''Sullivan'

or you may prefer to create a function which is passed a surname and checks each character inserting the relevant character where needed like so:

Function SQLStr(ByVal str As String)
Dim intPos As Integer

intPos = InStr(str, "'")
Do While intPos > 0
str = Left(str, intPos - 1) & "'" & Mid(str, intPos)
intPos = InStr(intPos + 2, str, "'")
Loop
SQLStr = str
End Function

so the WHERE clause will look like so


strSQL = "SELECT * FROM Employees " & _
"WHERE Surname = '" & SQLstr(strSurname) & "'"

Cheers, I thought that would be the way to do it.

Thanks for your help. :)