Multiple Joins Using DAO

[dave b via AccessMonster.com]

I have several pieces of information that I need to gather for my Downtime
form. I've coded a multiple join; when I activate the form, I get the
following error:

Syntax error (missing operator) in query expression . . .

and then it lists part of my code with the multiple joins.

My code is as follows:

-------------------------------------------
SQLString = "SELECT *" & vbCrLf
SQLString = SQLString & " FROM tblDowntime" & vbCrLf
SQLString = SQLString & " INNER JOIN tblDept" & vbCrLf
SQLString = SQLString & " ON tblDownTime.Dept = tblDept.ID" &
vbCrLf
SQLString = SQLString & " LEFT JOIN tblProcessStep" & vbCrLf
SQLString = SQLString & " ON tblDownTime.ProcessStep =
tblProcessStep.ProcessStep" & vbCrLf
SQLString = SQLString & " INNER JOIN tblReason" & vbCrLf
SQLString = SQLString & " ON tblDownTime.IDReason = tblReason.
IDReason" & vbCrLf
SQLString = SQLString & " INNER JOIN tblCat" & vbCrLf
SQLString = SQLString & " ON tblDownTime.IDCat = tblCat.IDCat"
& vbCrLf
SQLString = SQLString & " WHERE (tblDowntime.ProductID &
tblDowntime.LotNo ='" & [Forms]![frmMnMenu]![cboProductID] & [Forms]!
[frmMnMenu]![cboLotNumber] & "')"

 

[Douglas J Steele]

Try building the query through the query builder and looking at the SQL
that's generated. Access has a non-standard syntax for joins.

For instance, rather than

FROM Employees INNER JOIN Orders ON Employees.EmployeeID = Orders.EmployeeID
INNER JOIN [Order Subtotals] ON Orders.OrderID = [Order Subtotals].OrderID)

Access will use:

FROM Employees INNER JOIN (Orders INNER JOIN [Order Subtotals] ON
Orders.OrderID = [Order Subtotals].OrderID) ON Employees.EmployeeID =
Orders.EmployeeID

The inner parentheses are required: the query cannot be rewritten as

FROM Employees INNER JOIN Orders INNER JOIN [Order Subtotals] ON
Orders.OrderID = [Order Subtotals].OrderID ON Employees.EmployeeID =
Orders.EmployeeID