Issues using apostrophe

[rosscortb]

Hello,



When entering words with apostophe's in a comments section it produces and error message(see attached) so it would need to be Davids and not David's, I have noticed- is that normal?
Thanks
Ross

[lfpm062010]

Hope this will help you.
http://allenbrowne.com/casu-17.html

[rosscortb]

Hello,

Did I understand this correctly, that I need to edit the code, if so, can you indicate where please?

Private Sub cmdAddaComment_Click()
If IsNull(txtAddComment) Then
MsgBox "Please input in comment text."
txtAddComment.SetFocus
Else
Dim strSql As String
strSql = "insert into Comments ([IssueID], [CommentDate], [Comment], [UserID], [Task], [CompleteIn]) values ("
strSql = strSql & [ID] & ", Now(), '" + txtAddComment + "', " & TempVars![tmpUserID]
strSql = strSql & IIf(IsNull(cmbTask), ",null", ",'" & cmbTask & "'")
strSql = strSql & IIf(IsNull(txtCompleteIn), ",null)", ",'" & txtCompleteIn & "')")
DoCmd.SetWarnings False
DoCmd.RunSQL strSql
DoCmd.SetWarnings True
cmbTask = Null
txtAddComment = Null
txtCompleteIn = Null
sfrComments.Requery
End If
End Sub

Thanks

Ross

[John_G]

This error occurs because apostrophe's are the same character as single quotes, so the SQL statement has delimiters (the single quotes) where they should not be, causing a syntax error.

You can fix this by using double-quote delimiters around your string data as shown below. The quotes are typed twice so that they are interpreted as being part of the string, and not as delimiters in the statement.

strSql = "insert into Comments ([IssueID], [CommentDate], [Comment], [UserID], [Task], [CompleteIn]) values ("
strSql = strSql & [ID] & ", Now(), """ + txtAddComment + """, " & TempVars![tmpUserID]
strSql = strSql & IIf(IsNull(cmbTask), ",null", ",""" & cmbTask & """")
strSql = strSql & IIf(IsNull(txtCompleteIn), ",null)", ",""" & txtCompleteIn & """)")

But, making this change also means that you cannot use double-quotes in the data you are entering - you can't have it both ways.

(Actually, with VBA and careful programming you can set it up so that your data strings can contain either single-quotes (apostrophe's) OR double quotes, but not both. I can post that code if you need it)

[rosscortb]

Hi John,

Yes, please.

Every day is a school day.

Ross

[John_G]

Here is a copy of one of my procs that demonstrates this process. It is more-or-less verbatim, so some of it is not relevant to this discussion. I have hghighted the important parts in red.

Sub Sequence_Numbers_for_Duplicate_Text(TABLENAME As String, Optional WhereText As String = " ")
  Dim CurrentProcedure As String
  '******************************
  CurrentProcedure = "Sequence_Numbers_for_Duplicate_Text"
  Dim db As Database, rst As Recordset
  Dim SQL As String
  Dim DELIM As String
  Dim SingleQuote As Boolean, DoubleQuote As Boolean
  '******************************
  On Error GoTo ErrProc
  Set db = CurrentDb
  SQL = "Delete from Sequence_number_List"
  db.Execute SQL, dbFailOnError
  '
  ' Set the sql to create the recordset
  '
  If TABLENAME = "X_Job_Health_Risk_Detail" Or TABLENAME = "X_Job_Working_Conditions_Detail" Then
    SQL = "SELECT Left([Text_En],255) AS TextEn, Count(Text_En) AS [Count] " & _
          " FROM " & TABLENAME & _
          " WHERE " & WhereText & _
          " GROUP BY Left([Text_En],255) HAVING Count(Text_En)>1;"
  Else
    SQL = "SELECT Left([Text_En],255) AS TextEn, Count(Text_En) AS [Count] " & _
          " FROM " & TABLENAME & " GROUP BY Left([Text_En],255) HAVING Count(Text_En)>1;"
  End If
  Set rst = db.OpenRecordset(SQL)
  While Not rst.BOF And Not rst.EOF
    SingleQuote = InStr(rst!TextEN, "'") > 0
    DoubleQuote = InStr(rst!TextEN, """") > 0
    If SingleQuote And DoubleQuote Then
      MsgBox "Cannot check" & vbCrLf & rst!TextEN & vbCrLf & "It contains both single and double quotes", vbOKOnly + vbExclamation, "String too complex"
      GoTo Loop1
    End If
    '
    ' Set the string delimiters
    '
    If SingleQuote Then DELIM = """" Else DELIM = "'"
  
    '
    ' find the sequence numbers in TableName which have this English Text
    '
    If TABLENAME = "X_JOB_Statement" Then
      SQL = "Insert into Sequence_Number_List (Seq_Num) select Job_Statement_ID from " & TABLENAME & _
          " Where left(text_en,255) = " & DELIM & rst!TextEN & DELIM
      db.Execute SQL, dbFailOnError
    ElseIf TABLENAME = "X_Job_Health_Risk_Detail" Or TABLENAME = "X_Job_Working_Conditions_Detail" Then
      SQL = "Insert into Sequence_Number_List select Seq_Num from " & TABLENAME & _
          " Where left(text_en,255) = " & DELIM & rst!TextEN & DELIM & " AND " & WhereText
      db.Execute SQL, dbFailOnError
    Else
      SQL = "Insert into Sequence_Number_List select Seq_Num from " & TABLENAME & _
          " Where left(text_en,255) = " & DELIM & rst!TextEN & DELIM
      db.Execute SQL, dbFailOnError
    End If
Loop1:
    rst.MoveNext
  Wend
  rst.Close
  Exit Sub
ErrProc:
  '// Error Handling
  Process_Error CurrentForm, CurrentProcedure, Err.Description
  '// Terminate
  On Error Resume Next
  rst.Close
  Set db = Nothing
  '// Clear Error
  Err.Clear
End Sub

One other advantage with this is that I don't have to fiddle around trying to get the delimiters in the proper place and format - it's a lot easier to write using DELIM.

Your code will be more complex (but not really more difficult) if each of the string values you use can contain single- or double-quotes, because you will have to set or reset the DELIM for each one, and concatenate the results to the SQL string.

Post back if you need additional clarification.

John