The function should be placed in a Module and needs to be declared Public.
You can paste this code directly into your module.
Public Function FixQuotes(strQuoted As String) As String
' Purpose Double up single quotes for use in a Sql Update 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
If you had a text box on your form called txtYourTextBox that contained the
last name that you wanted to select on then you might use it like this:
strSql=Select * FromAnyTable where LastName= '" &
FixQuotes(txtYourTextBox.Value) & "' OrderBy LastName;"
If the name in txtYourTextBox was O'Neil then strSql would be:
Select * FromAnyTable where LastName= 'O''Neil' OrderBy LastName;
If the Name in txtYourTextBox was Smith then strSql would be:
Select * FromAnyTable where LastName= 'Smith' OrderBy LastName;
In either case the results for the query would be correct.
You can test the function for proper operation from the Immediate window
(hit CTRL+G to make it visible) like this:
Type ? fixQuotes("O'Neil") in the Immediate window and hit Enter.
On the next line Access Will print O''Neil
Ron W
