Apostrophe causes error in SQL string: Solution

[sashi]

Apostrophe

Have you ever tried to send a string variable to MS Access that had apostrophes embedded within an SQL Statement? If YES you will get a run time ERROR. Here is your solution, a function that formats the variable before sending it to the database.

Public Function Apostrophe(sFieldString As String) As String
  If InStr(sFieldString, "'") Then
    Dim iLen As Integer
    Dim ii As Integer
    Dim apostr As Integer
    iLen = Len(sFieldString)
    ii = 1
 
      Do While ii <= iLen
        If Mid(sFieldString, ii, 1) = "'" Then
          apostr = ii
          sFieldString = Left(sFieldString, apostr) & "'" & _
          Right(sFieldString, iLen - apostr)
          iLen = Len(sFieldString)
          ii = ii + 1
        End If
      ii = ii + 1
      Loop
  End If
 
  Apostrophe = sFieldString
End Function