Row W (fixquotes)

  • Thread starter Thread starter Lisa
  • Start date Start date
L

Lisa

I used the function, but everytime it hits that line it
says 'expected sub or variable' something like that and
gives error, why?
 
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
 
Back
Top