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
– Den, Commented Oct 17, 2017 at 22:36
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
– KacireeSoftware, Commented Oct 17, 2017 at 22:56
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
– KacireeSoftware, Commented Oct 17, 2017 at 22:58
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
– Parfait, Commented Oct 18, 2017 at 0:25

KacireeSoftware · Accepted Answer · 2017-10-18 12:40:30Z

1

 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.

edited Oct 18, 2017 at 12:40

answered Oct 17, 2017 at 23:08

KacireeSoftware

8086 silver badges20 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

KacireeSoftware Over a year ago

As a side note: I would personally avoid using error and DateTime as field or table names

KacireeSoftware Over a year ago

@Parfait I agree. I figured he was simply using some generic names here for the purpose of getting his SQL right.

Collectives™ on Stack Overflow

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

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related