• Home
 · 
• Miscellaneous
 · 
• Modify text entered so can be passed to Access via a SQL statement. Deals with CRLFs, Single and Do

Modify text entered so can be passed to Access via a SQL statement. Deals with CRLFs, Single and Do

Modify text entered so can be passed to Access via a SQL statement. Deals with CRLFs, Single and Double quotes.

Rate Modify text entered so can be passed to Access via a SQL statement. Deals with CRLFs, Single and Do

• 1
• 2
• 3
• 4
• 5

(1(1 Vote))

Public Function Parse_SQL_Text(strIn As String) As String

    On Error GoTo VBError
    
    Dim iAcnt As Long
    For iAcnt = 1 To Len(strIn)
    
        Select Case Asc(Mid(strIn, iAcnt, 1))
        
            Case 10, 13 'CrLf characters ok to pass
                
                Parse_SQL_Text = Parse_SQL_Text & Mid(strIn, iAcnt, 1)
                
            Case 124    'Pipe char, Only char I cant pass properly,
                        'so replace with Exp pt or Space
            
                Parse_SQL_Text = Parse_SQL_Text & "!"
                'Parse_SQL_Text = Parse_SQL_Text & " "
                
            Case 39     'Single Quote
                Parse_SQL_Text = Parse_SQL_Text & "''"
                
            Case 34     'Double quote
                Parse_SQL_Text = Parse_SQL_Text & """"
                                    
            Case Is < 32    'Out of Ascii Range
                Parse_SQL_Text = Parse_SQL_Text & " "
            
            Case Is > 126   'Out of Ascii Range
                Parse_SQL_Text = Parse_SQL_Text & " "
            
            Case Else 'Normal Ascii, send normally
                Parse_SQL_Text = Parse_SQL_Text & Mid(strIn, iAcnt, 1)
                
            End If
        
    Next iAcnt
     
    Exit Function
    
VBError:
  
    MsgBox "VBError in Sub Parse_SQL_Text : " & Err.Number & " - " & Err.Description
    If gDebug = True Then Resume Next
   
End Function

To Use (updating a table called DataTable, field DataField with Database object gDbase:

gDbase.Execute ("Update Data SET DataField = '" & Parse_SQL_Text(txtDataIn) _
                & "' WHERE Auto_ID = " & txtCurrID)