0

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 & ")"
Parfait
  • 104,375
  • 17
  • 94
  • 125
Den
  • 11
  • 1
  • 1
  • Also, would it be better to use NOT EXISTS instead of NOT IN? or is it similar in terms of speed? – Den Oct 17 '17 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 Oct 17 '17 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 Oct 17 '17 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](https://support.office.com/en-us/article/Access-2007-reserved-words-and-symbols-E33EB3A9-8BAA-4335-9F57-DA237C63EABE). – Parfait Oct 18 '17 at 00:25
  • [How to debug dynamic SQL in VBA](http://stackoverflow.com/questions/418960/managing-and-debugging-sql-queries-in-ms-access/1099570#1099570) – Andre Oct 18 '17 at 12:42

1 Answers1

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:

  1. 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
  1. 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.
KacireeSoftware
  • 798
  • 5
  • 19