Access VBA SQL Run-time error '3075' Syntax error

Question

I'm trying to run this code but it gives me the error message:

Run-time error '3075'; "Syntax error(missing operator) in query expression"

How should I fix this error?

I would like insert CellNumber that is in table1 but not in Variable table into ERROR table with DateTime, string, and CellNumber

Dim sqlstr As String

sqlstr = "SELECT CellNumber FROM table1 WHERE CellNumber NOT IN (SELECT CellNumber FROM Variable)"

DoCmd.RunSQL "INSERT INTO ERROR ([DateTime], RowNum, Error) SELECT Now(), 'string' as RowNum, CellNumber FROM(" & sqlstr & ")"

Also, would it be better to use NOT EXISTS instead of NOT IN? or is it similar in terms of speed? — Den
THe resultant SQL you are trying to run is : INSERT INTO ERROR ([DateTime], RowNum, Error) SELECT Now(), 'string' as RowNum, CellNumber FROM(SELECT CellNumber FROM table1 WHERE CellNumber NOT IN (SELECT CellNumber FROM Variable)) I think your problem lies in ", CellNumber FROM(SELECT CellNumber FROM " — KacireeSoftware
SO all you want to do is find all the cellnumbers in table1 that do not exist in Variable table? And then insert them into Error table? — KacireeSoftware
No need to wrap the above SELECT in parentheses within below's FROM as query expects a table alias. And add space after FROM. Also, escape reserved words. — Parfait

KacireeSoftware KacireeSoftware · Accepted Answer · 2017-10-17T23:08:41

 Dim sqlstr As String
    sqlstr = "INSERT INTO Error ( [DateTime],RowNum, Error ) " & _
             "SELECT Now() , 'String' , table1.cellnumber FROM table1 " & _
             "LEFT JOIN Variable ON table1.cellnumber = Variable.cellnumber " & _
             "WHERE  Variable.cellnumber Is Null"
    currentdb.execute sqlstr, dbfailonerror

That's how I would do it

Edit------ I would like to add for posterity a few comments:

I would personally set my DateTime stamp field with a default value of Now() and omit it from my sql altogether...All new records would get time-stamped with less room for error.

Dim sqlstr As String
        sqlstr = "INSERT INTO tblError ( StringField, CellNumber ) " & _
                 "SELECT 'String' , table1.cellnumber FROM table1 " & _
                 "LEFT JOIN table2 ON table1.cellnumber = table2.cellnumber " & _
                 "WHERE  table2.cellnumber Is Null"
        currentdb.execute sqlstr, dbfailonerror

I would never use DateTime, Error, or Variable as a table or field name, because even if access can deal with it (by delimiting reserved names [DateTime], for Instance), it can be confusing and misleading when trying to troubleshoot your code and sql.

Access VBA SQL Run-time error '3075' Syntax error

1 Answers