• Home
 · 
• Databases/Data Access/DAO/ADO
 · 
• Aprostrophe 'The Rebirth'

Aprostrophe 'The Rebirth'

Have you ever try so send a SQL String to a database that has apostrophes ? 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

Inputs
Ziltch

Assumes
Take a string, looks for Aprostrophes or Quotation marks appearing more than twice between commas, if so it will double them up.

Rate Aprostrophe 'The Rebirth'

• 1
• 2
• 3
• 4
• 5

(42(42 Vote))

Public Function Apos2(strSQL As String) As String
 Dim F As Long, N As Long, Q As Long
 Dim O As String, P As String, A As String
 Q = -1
 For F = 1 To Len(strSQL)
  P = Mid(strSQL, F, 1)
  If P = "'" Or P = """" Then
   If Q > 0 Then
    O = O + "'" + A
    A = ""
   End If
   Q = Q + 1
  ElseIf P = "," Then
   O = O & A
   Q = -1
   A = ""
  End If
  If Q <= 0 Then
   O = O & P
  Else
   A = A & P
  End If
 Next
 Apos2 = O & A
End Function


24 Jan 00
Some Alterations,
and some documentation,
Though F stays in the loop, for sentimental reasons
Public Function Apos3(strSQL As String) As String

'F is the current position in the original string
'lCountOfApos Counts the occurrences of apostrophes and quotes
'lCharaterAtPositionF equals the Character at position F
'If lCharaterAtPositionF is equal to a apostrophes or quote Then
'If lCountOfApos grater than zero
'Then add a additional apostrophe to sOutput along with sBuffer
'sBuffer is a Buffer that is used to store characters after the Second
'occurrence of a apostrophes or quote whilst not encountering a Comma, Quote or apostrophe
'Clear as mud
  Dim F As Long, lCountOfApos As Long
  Dim sOutput As String, lCharaterAtPositionF As String, sBuffer As String
  lCountOfApos = -1
  For F = 1 To Len(strSQL)
    lCharaterAtPositionF = Mid(strSQL, F, 1)
    If lCharaterAtPositionF = "'" Or lCharaterAtPositionF = """" Then
      If lCountOfApos > 0 Then
        sOutput = sOutput + "'" + sBuffer
        sBuffer = ""
      End If
      lCountOfApos = lCountOfApos + 1
    End If
    
    If lCountOfApos <= 0 Then
      sOutput = sOutput & lCharaterAtPositionF
    Else
      sBuffer = sBuffer & lCharaterAtPositionF
      If lCharaterAtPositionF = "," Or Right(sBuffer, 5) = " AND " Or Right(sBuffer, 4) = " OR " Then
        
        sOutput = sOutput & sBuffer
        lCountOfApos = -1
        sBuffer = ""
        
      End If
    End If
  Next
  Apos3 = sOutput & sBuffer
End Function