Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2794
  • Last Modified:

Apostrophe included in SQL Insert string

I'm sure someone has the perfect answer for this. what is the trick to inserting text into a table via sql/vba that includes apostrophe's? (Or other characters that will break it for that matter?)

For instance, I am using this code:
For j = 0 To 1
            
        'Save Row and Column Descriptions
        
        If j = 0 Then c = "Row" Else c = "Col"
        
        strSQL = "INSERT INTO tbHRI_Descrip (HRI_ID,Descrip_ID,DescripType,Label," & _
            "Description)" & _
            "VALUES ('" & Me.txtHRI_ID.Value & "','" & i & "','" & c & "','" & _
            Me.Controls("txt" & c & "Label" & i).Value & "','" & _
            Me.Controls("txt" & c & "Descrip" & i).Value & "')"
        
        DoCmd.RunSQL strSQL
              
        Next

Open in new window

0
adraughn
Asked:
adraughn
  • 2
  • 2
2 Solutions
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
you have to duplicate the quote. assuming that only Lable is subject to contain a quote:



For j = 0 To 1
            
        'Save Row and Column Descriptions
        
        If j = 0 Then c = "Row" Else c = "Col"
        
        strSQL = "INSERT INTO tbHRI_Descrip (HRI_ID,Descrip_ID,DescripType,Label," & _
            "Description)" & _
            "VALUES ('" & Me.txtHRI_ID.Value & "','" & i & "','" & c & "','" & _
            Me.Controls("txt" & c & "Label" & i).Value & "','" & _
            replace(Me.Controls("txt" & c & "Descrip" & i).Value , "'", "''") & "')"
        
        DoCmd.RunSQL strSQL
              
        Next

Open in new window

0
 
Rey Obrero (Capricorn1)Commented:
wrap it with chr(34)

 " & chr(34) & variablestring & chr(34) & "
0
 
adraughnAuthor Commented:
so in angel's solution, the table will show an apostrophe as a double apostrophe? So then when I populate the form with the recordset, i would need to replace the double apostrophe with an apostrophe? Would this also replace quotes? (")

in cap's solution, is chr(34) an apostrophe? So would it store the string in the table as 'string's' instead of string's?
0
 
Rey Obrero (Capricorn1)Commented:
chr(34) is double quote { " }

it will store the value as  string's ., ex. O'Brien    
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
>so in angel's solution, the table will show an apostrophe as a double apostrophe?
no. duplicating the single quote into 2 single quotes is ONLY for the insert to work.
after the insert, there will indeed only be 1 single quote in the table.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Get 10% Off Your First Squarespace Website

Ready to showcase your work, publish content or promote your business online? With Squarespace’s award-winning templates and 24/7 customer service, getting started is simple. Head to Squarespace.com and use offer code ‘EXPERTS’ to get 10% off your first purchase.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now