Syntax error in Join Operation 1

[Xzibit7]

I have a query that works perfectly now, However I am trying to add another field from a table and I keep getting an error message that says Syntax error in Join operation, when I try to save the changes. The problem is even if I just erase one letter from the original sql statement and then put it back the same way, when I try to save it the same error message pops up. Here is the SQL statement

SELECT S.*
FROM ([SELECT Supervisor.ID, Supervisor.NIIN, Supervisor.Nomenclature, Supervisor.Assignment, Supervisor.[Date Assigned], Supervisor.Closed, Supervisor.Requestor, Supervisor.[Requestor Email], Employee.[Follow-Up Date], Supervisor.[Anticipated Completion Date], Supervisor.AssignedTo
FROM Supervisor LEFT JOIN Employee ON Supervisor.ID = Employee.ID
WHERE (((Supervisor.Closed)=No))]. AS S INNER JOIN Userinfo AS U ON S.assignedto=U.username) INNER JOIN Userinfo AS C ON U.GroupId=C.GroupId
WHERE C.username = CurrentUser();

 

[Golom]

Its because Access puts the characters in [red]red[/red] in place of parentheses.

SELECT S.*
FROM ([red][[/red]SELECT Supervisor.ID, Supervisor.NIIN, Supervisor.Nomenclature, Supervisor.Assignment, Supervisor.[Date Assigned], Supervisor.Closed, Supervisor.Requestor, Supervisor.[Requestor Email], Employee.[Follow-Up Date], Supervisor.[Anticipated Completion Date], Supervisor.AssignedTo
FROM Supervisor LEFT JOIN Employee ON Supervisor.ID = Employee.ID
WHERE (((Supervisor.Closed)=No))[red]].[/red] AS S INNER JOIN Userinfo AS U ON S.assignedto=U.username) INNER JOIN Userinfo AS C ON U.GroupId=C.GroupId
WHERE C.username = CurrentUser();

which should be

SELECT S.*
FROM ([red]([/red]SELECT Supervisor.ID, Supervisor.NIIN, Supervisor.Nomenclature, Supervisor.Assignment, Supervisor.[Date Assigned], Supervisor.Closed, Supervisor.Requestor, Supervisor.[Requestor Email], Employee.[Follow-Up Date], Supervisor.[Anticipated Completion Date], Supervisor.AssignedTo
FROM Supervisor LEFT JOIN Employee ON Supervisor.ID = Employee.ID
WHERE (((Supervisor.Closed)=No))[red])[/red] AS S INNER JOIN Userinfo AS U ON S.assignedto=U.username) INNER JOIN Userinfo AS C ON U.GroupId=C.GroupId
WHERE C.username = CurrentUser();

The only solution that I know of (apart from changing the statement back to the second form manually) is to make the sub-query a separate query.
Save this as [blue]qrySupvEmp[/blue] (for example)

SELECT Supervisor.ID, Supervisor.NIIN, Supervisor.Nomenclature, Supervisor.Assignment, Supervisor.[Date Assigned], Supervisor.Closed, Supervisor.Requestor, Supervisor.[Requestor Email], Employee.[Follow-Up Date], Supervisor.[Anticipated Completion Date], Supervisor.AssignedTo
FROM Supervisor LEFT JOIN Employee ON Supervisor.ID = Employee.ID
WHERE (((Supervisor.Closed)=No))

And then

SELECT S.*
FROM qrySupvEmp AS S INNER JOIN Userinfo AS U 
     ON S.assignedto = U.username 
     INNER JOIN Userinfo AS C 
     ON U.GroupId=C.GroupId
WHERE C.username = CurrentUser();

 

[Xzibit7]

Thank You