'Example O'Toole' how to delimate a '

[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

 

[Douglas J. Steele]

You need to replace each occurrence of ' in the string with two '' in a row.

Assuming you're using Access 2000 or newer, try the following:

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

If you're using Access 97 or older, you'll need to provide your own
equivalent function to Replace.