Using select with a ' in the value ?

[Chris Joyce]

I'm sure this is a real simple one ,

whats the best trick when your select conditions have a ' in the value
"WHERE (People.FullName = 'Example O'Toole' )" ?

Chris


Example

strSQLPeople = "SELECT People.FullName "
strSQLPeople = strSQLPeople & "FROM People "
strSQLPeople = strSQLPeople & "WHERE (People.FullName = '" &
Me.ComboFullName.Value & "' ) "
strSQLPeople = strSQLPeople & "GROUP BY People.FullName "

Set rstPeople = New ADODB.Recordset
rstPeople.CursorType = adOpenStatic
rstPeople.CursorLocation = adUseClient


Debug.Print strSQLPeople

' strSQLPeople example
' SELECT People.FullName FROM People WHERE (People.FullName = 'Example
O'Toole' ) GROUP BY People.FullName

rstPeople.Open strSQLPeople , CurrentProject.Connection, adOpenKeyset,
adLockOptimistic, adCmdText

 

[Ron Weiner]

The practice I have always followed is to double up all single quotes using
the following function in a global module:

Public Function FixQuotes(strQuoted As String) As String
' Purpose Double up single quotes for use in a Sql query
Dim i As Integer, strOut As String

strOut = ""
For i = 1 To Len(strQuoted)
If Mid(strQuoted, i, 1) = Chr(39) Then
strOut = strOut & Chr(39)
End If
strOut = strOut & Mid(strQuoted, i, 1)
Next
FixQuotes = strOut
End Function

This function works in all versions of Access (Believe it or not I still
help support one Access 2 database and a number of Access 97 databases). So
in your scenario I'd code like this:

strSQLPeople = strSQLPeople & "WHERE (People.FullName = '" &
FixQuotes(Me.ComboFullName.Value) & "' ) "

This will result in the following string getting passed to database

....WHERE (People.FullName = 'O''Toole' ) ...

which will produce the desired result.

Ron W

 

[Roger Carlson]

In your WHERE clause, replace each apostrophe (') with 2 quotes (""). Like
this:
strSQLPeople = strSQLPeople & "WHERE (People.FullName = """ &
Me.ComboFullName.Value & """ ) "

It looks odd, but it works.