Access VBA 'Syntax Error in Join Operation'

Question

I'm trying to attach a SELECT query to a VBA object in Access, but I keep getting the error:

Syntax Error in Join operation

Dim sql As String
sql = "SELECT R_History.iteration, R.RuntimeID, Policylanguage.FormBody " & _
      "FROM PolicyLanguage " & _
      "INNER JOIN ([Policy&Elements] " & _
      "INNER JOIN ((R " & _
      "INNER JOIN R_History " & _
      "ON (R.RunTimeID = R_History.RunTimeID " & _
      "AND (R.ReportID = R_History.ReportID)) " & _
      "INNER JOIN StatePolicyLanguage " & _
      "ON R_History.Iteration = StatePolicyLanguage.Iteration) " & _
      "ON [Policy&Elements].[Text&ElementID] = StatePolicyLanguage.[TextID&ElementID] " & _
      "ON PolicyLanguage.TextID = [Policy&Elements].TextID " & _
      "WHERE (((R.RuntimeID) = Dlast('runtimeID','r')));"
DoCmd.RunSQL (sql)

I can't get this to run for the absolute life of me. Can anyone tell what's wrong?

Edit: Here is the code I am trying to emulate

each inner join should be followed by the corresponding ON statement — apomene
You have two ON statements there without parentheses between them. You're missing some parentheses. Also, writing each ON statement after the corresponding join, like @apomene said, is a good plan (though it's not required, you can first write all the joins and then all the on clauses and group them using parentheses, but that results in illegible code, like you have now). — Erik A
This is pretty much taken right from SQL view in Access (which works). I'm fairly new to mixing VBA and SQL. I've input a few successfuly but this is definitely the most complex so far, so pardon my ignorance but would I need more ON statements if it's working fine in SQL view? — VBAmazing
DoCmd.RunSQL is used for action queries no resultset queries. Please advise what you are trying to do: Open recordset? Update form/report recordsource? And why are you not saving the query (more efficient than a VBA string query as engine can compile it with best execution plan including indexes)? — Parfait
I have a button on a form fires a query based on data from the current entry. No updating of any records, just display. My plan was to use VB to set variables to var1=me.field1,var2=me.field2 (where both vars are string), and then concatenate those into the query-- but that isn't the issue in my particular question. — VBAmazing

Zack Zack · Accepted Answer · 2018-06-26T19:48:41

If you're running a query where the criteria are values entered in a form (e.g. field1 = frm1.txtValue), you could create a parameterized query where the passed parameters are added to the WHERE clause. That way, you don't have create SQL in VBA---you'd just have to set the parameters in VBA.

As noted by others, Access requires you to put parentheses after each join:

from (((Table1
    inner join Table2 on Table2.field1 = Table1.field1)
    inner join Table3 on Table3.field2 = Table2.field2)
    inner join Table4 on Table4.field3 = Table3.field3)

So, as suggested by others, if you absolutely need to construct the query via VBA, keep your JOIN and ON clauses together to prevent confusion, and add the necessary parentheses.

I've found when you have to create complex queries in Access (either in the SQL editor or in VBA), it can be helpful to write the SQL in a more friendly editor (e.g. Notepad++) that supports things like syntax highlighting. Then, you can paste the query where it needs to go.

Access VBA 'Syntax Error in Join Operation'

1 Answers