0

I am trying to insert two columns(conceptname, storeNumberID) values from lodgingReport into temptable. But there is a syntax error and I can't find the fix

db.Execute "Select [Concept Name],[Store Number ID] into" & TempTable & "from TblLodgingReport"

Full function (inserted from comments):

Function CreateTempTable() 
  Dim db As Database 
  Set db = CurrentDb() 
  db.Execute "Select [Concept Name],[Store Number ID] into" & TempTable & _
             "from TblLodgingReport" 
  MsgBox "table is created" 
End Function
Erik A
  • 31,639
  • 12
  • 42
  • 67
  • 3
    You're missing spaces. Add spaces before and after the `"` quotes: `"Select [Concept Name],[Store Number ID] into " & TempTable & " from TblLodgingReport"` – Erik A May 01 '18 at 20:01
  • I tried adding spaces. Still the same error –  May 01 '18 at 20:09
  • Eh... Then you're probably doing something weird like including a space in your table name. Add square brackets: `"Select [Concept Name],[Store Number ID] into [" & TempTable & "] from TblLodgingReport"` – Erik A May 01 '18 at 20:15
  • Still no luck :( –  May 01 '18 at 20:19
  • 1
    Include the full code. There are probably errors elsewhere. – Erik A May 01 '18 at 20:20
  • Here you go Function CreateTempTable() Dim db As Database Set db = CurrentDb() db.Execute "Select [Concept Name],[Store Number ID] into" & TempTable & "from TblLodgingReport" MsgBox "table is created" End Function –  May 01 '18 at 20:25
  • 1
    Eh... You've defined the variable `TempTable` nowhere. Do you just want to do `"Select [Concept Name],[Store Number ID] into [TempTable] from TblLodgingReport"`? – Erik A May 01 '18 at 20:41
  • You did not declare the variable "TempTable" anywhere in the code you listed. That is most likely the problem you are encountering. Basically your code says to run this query : "Select [Concept Name], [Store Number ID] into from TblLodgingReport" – ccarpenter32 May 01 '18 at 20:43
  • Should I declare Temptable as variant or as tabledefs? –  May 01 '18 at 20:46
  • Dim TempTable As String – Gustav May 01 '18 at 20:48
  • Even with string, its the same error –  May 01 '18 at 20:49
  • TempTable = "temptable" (the string needs to be setup) – ccarpenter32 May 01 '18 at 21:00
  • Its throwing new error. Here is new code: Query input must contain atleast one table or query Dim db As Database Dim Temptable As String Temptable = "temptable" Set db = CurrentDb() db.Execute "Select [Concept Name],[Store Number ID] into " & Temptable & "from [TblLodgingReport];" MsgBox "table is created" –  May 01 '18 at 21:08
  • Looks like the space before "from" is still missing. It should be " from". Right now, the SQL reads : `Select [Concept Name],[Store Number ID] into temptablefrom [TblLodgingReport]` – ccarpenter32 May 01 '18 at 21:11
  • It worked!!!!! Thank you So much –  May 01 '18 at 21:13
  • For future reference: [How to debug dynamic SQL in VBA](http://stackoverflow.com/questions/418960/managing-and-debugging-sql-queries-in-ms-access/1099570#1099570) – Andre May 02 '18 at 11:50

0 Answers0