VBA SQL String Run-Time Error 3075

Question

I have a string that is adding data to a table so I can print a report or labels from the data. The data consists of addresses and is causing the string to fail because of the comma in the address. this string has been working but when it has some weird addresses I think that is what is causing this.

sqls = "INSERT INTO tInvReportDataWrk(SO,ITEM,QTY,billTO,shipTO,LINEKEY)VALUES('" & 
  SO & "', '" & it & "', '" & qty & "', '" & billTO & "', '" & shipTO & "', '" & lk & "')"

The data that is trying looks like this from the debug.print

INSERT INTO tInvReportDataWrk(SO,ITEM,QTY,billTO,shipTO,LINEKEY)
VALUES('0000001', 'L-R_4-8R2B-01', '2', 'BAR - ANAHEIM
BAR BRANCH
P.O. BOX 00000
VENDOR ID# VC-00001
Saint Louis, MO  00008
', 'ABC ELEMENT WAREHOUSE
2000 O'TOOL AVE.
San Jose, CA  95131-1301
', '000001')

Answer 1

I'm uncertain whether the comma in the address is the problem. It looks to me like the apostrophe in O'TOOL should be a problem. But if it is not the cause of the first error Access complains about, it should trigger another error after you fix the first error.

For a simple example, the following code triggers error 3075, "Syntax error (missing operator) in query expression ''O'TOOL');'."

Dim strInsert As String
strInsert = "INSERT INTO tblFoo(text_field) VALUES ('O'TOOL');"
CurrentDb.Execute strInsert, dbFailOnError

Changing the INSERT statement to either of these allows the statement to execute without error.

strInsert = "INSERT INTO tblFoo(text_field) VALUES ('O''TOOL');"
strInsert = "INSERT INTO tblFoo(text_field) VALUES (""O'TOOL"");"

You could revise your code to use one of those approaches. However consider a parameter query or the DAO.Recordset.AddNew method instead ... and then quotes and apostrophes will be less troublesome.

Answer 2

Yes, it is the apostrophe.

You can use this function to avoid this and most other troubles when concatenating SQL:

' Converts a value of any type to its string representation.
' The function can be concatenated into an SQL expression as is
' without any delimiters or leading/trailing white-space.
'
' Examples:
'   SQL = "Select * From TableTest Where [Amount]>" & CSql(12.5) & "And [DueDate]<" & CSql(Date) & ""
'   SQL -> Select * From TableTest Where [Amount]> 12.5 And [DueDate]< #2016/01/30 00:00:00#
'
'   SQL = "Insert Into TableTest ( [Street] ) Values (" & CSql(" ") & ")"
'   SQL -> Insert Into TableTest ( [Street] ) Values ( Null )
'
' Trims text variables for leading/trailing Space and secures single quotes.
' Replaces zero length strings with Null.
' Formats date/time variables as safe string expressions.
' Uses Str to format decimal values to string expressions.
' Returns Null for values that cannot be expressed with a string expression.
'
' 2016-01-30. Gustav Brock, Cactus Data ApS, CPH.
'
Public Function CSql( _
    ByVal Value As Variant) _
    As String

    Const vbLongLong    As Integer = 20
    Const SqlNull       As String = " Null"

    Dim Sql             As String
    Dim LongLong        As Integer

    #If Win32 Then
        LongLong = vbLongLong
    #End If
    #If Win64 Then
        LongLong = VBA.vbLongLong
    #End If

    Select Case VarType(Value)
        Case vbEmpty            '    0  Empty (uninitialized).
            Sql = SqlNull
        Case vbNull             '    1  Null (no valid data).
            Sql = SqlNull
        Case vbInteger          '    2  Integer.
            Sql = Str(Value)
        Case vbLong             '    3  Long integer.
            Sql = Str(Value)
        Case vbSingle           '    4  Single-precision floating-point number.
            Sql = Str(Value)
        Case vbDouble           '    5  Double-precision floating-point number.
            Sql = Str(Value)
        Case vbCurrency         '    6  Currency.
            Sql = Str(Value)
        Case vbDate             '    7  Date.
            Sql = Format(Value, " \#yyyy\/mm\/dd hh\:nn\:ss\#")
        Case vbString           '    8  String.
            Sql = Replace(Trim(Value), "'", "''")
            If Sql = "" Then
                Sql = SqlNull
            Else
                Sql = " '" & Sql & "'"
            End If
        Case vbObject           '    9  Object.
            Sql = SqlNull
        Case vbError            '   10  Error.
            Sql = SqlNull
        Case vbBoolean          '   11  Boolean.
            Sql = Str(Abs(Value))
        Case vbVariant          '   12  Variant (used only with arrays of variants).
            Sql = SqlNull
        Case vbDataObject       '   13  A data access object.
            Sql = SqlNull
        Case vbDecimal          '   14  Decimal.
            Sql = Str(Value)
        Case vbByte             '   17  Byte.
            Sql = Str(Value)
        Case LongLong           '   20  LongLong integer (Valid on 64-bit platforms only).
            Sql = Str(Value)
        Case vbUserDefinedType  '   36  Variants that contain user-defined types.
            Sql = SqlNull
        Case vbArray            ' 8192  Array.
            Sql = SqlNull
        Case Else               '       Should not happen.
            Sql = SqlNull
    End Select

    CSql = Sql & " "

End Function

Answer 3

The issue is due to the '(apostrophes) in "O'TOOL". Try replacing that with double apostrophe (O''TOOL) or put the address in double quotes