Join within INNER JOIN

[cumap]

Something not right with this query, please help
Here is the error:
Microsoft OLE DB Provider for ODBC Drivers (0x80040E14)
[Microsoft][ODBC Microsoft Access Driver] Syntax error in FROM clause.

"SELECT " _
	& "	a.idGroup,a.groupName,a.viewType,a.active, " _
	& "	b.idCust,c.custName " _
	& "	FROM tblGroups a " _
	& "	INNER JOIN tblGroupCust b " _
	& "		INNER JOIN tblCustomers c " _
	& "			ON b.idCust = c.idCust " _
	& "		ON a.idGroup = b.idGroup " _
	& "	ORDER BY a.groupName"

If I remove the inner INNER JOIN, I will get the b.idCust just fine. The c.custName is the problem.

Thanks

 

[cumap]

I found out why and the problem is: missing quotation before tblGroupCust b and after the ON first ON case.

Stupid me!

 

[PHV]

Another way:

"SELECT " _
    & "    a.idGroup,a.groupName,a.viewType,a.active, " _
    & "    b.idCust,c.custName " _
    & "    FROM (tblGroups a " _
    & "    INNER JOIN tblGroupCust b " _
    & "        ON a.idGroup = b.idGroup) " _
    & "    INNER JOIN tblCustomers c " _
    & "        ON b.idCust = c.idCust " _
    & "    ORDER BY a.groupName"

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886