Single Quote in SQL Statement

[Dan]

I need to run a insert sql statement into one of my
tables. As you can see, the sub get arguments that need to
be put into the sql statement. The problem that I am
getting is that if the BD argument has a single quote in
its value (Ex: 10' feet CAT5 Cable), the sql statement
returns a error. How can you make the statement handle
strings that have a single quote.

Dan
=======================================================
Public Sub BUNDLE(PON, ID, UP, itid, QTY, BD, p)
Dim i As Integer 'counter
DoCmd.SetWarnings (warningsoff)
i = 0
Do Until i = QTY

DoCmd.RunSQL ("Insert Into tbl_po_details ([POID],[ItemID],
[DESCRIPTION],[Quantity],[Unit_Price],
[ITEM_TYPE_ID_FOR_SELECTION],[GROUP_ON_PO],[PRODUCT])
values (" & PON & "," & ID & ",'" & BD & "',1," & UP & ","
& itid & ",-1,'" & p & "')")

End Sub

 

[Van T. Dinh]

Try:

DoCmd.RunSQL ("Insert Into tbl_po_details ([POID],[ItemID],
[DESCRIPTION],[Quantity],[Unit_Price],
[ITEM_TYPE_ID_FOR_SELECTION],[GROUP_ON_PO],[PRODUCT])
values (" & PON & "," & ID & "," & Chr$(34) & BD & Chr$(34) & ",1," &
UP & "," & itid & ",-1,'" & p & "')")

BTW, it is easier to debug in you can construct the SQL String separately
and print the result of the concatenation so that you can see exactly what
is passed to JET database engine for processing. Something like:

Dim strSQL As String

strSQL = {as above}

Debug.Print strSQL

DoCmd.RunSQL strSQL

....

You may also like to check out the Execute Method which can also be used to
execute an Action SQL String which doesn't require confirmation.

 

[Joe Fallon]

You may want to call a function like this to handle the quotes:

Public Function HandleQuotes(strValue As String, Optional strDelimiter As
String = """") As String
On Error GoTo Err_HandleQuotes

' In:
' strValue: Value to fix up.
' strDelimiter: (Optional) Delimiter to use.
' Out:
' Return value: the text, with delimiters fixed up.
' Example:
' HandleQuotes("John ""Big-Boy"" O'Neil") returns
' "John " & Chr$(34) & "Big-Boy" & Chr$(34) & " O'Neil"

Dim strInsert As String
strInsert = "Chr$(" & Asc(strDelimiter) & ")"
HandleQuotes = strDelimiter & Replace(strValue, strDelimiter, strDelimiter
& " & " & strInsert & " & " & strDelimiter) & strDelimiter

Exit_HandleQuotes:
Exit Function

Err_HandleQuotes:
MsgBox "Error # " & Err.Number & " was generated by " & Err.Source &
vbCrLf & Err.Description, , "SystemCode - HandleQuotes"
Resume Exit_HandleQuotes

End Function