Need help with parenthesis placement for left join.

[Axeia]

Hello, I'm having difficulties again with these darned parenthesis access requires. Query is throwing a "Missing operator" error.

This is the query

SELECT S.Stoelnummer AS Stoelnummer, S.Rang AS Rang, dbo_Prijzen.Prijs AS Prijs, IIf(IsNull(BS.Stoelnummer),'','X') AS Gereserveerd, IIf(IsNull(PS.Stoelnummer),'','X') AS GereserveerdDoorDezePersoon
FROM dbo_Prijzen 
INNER JOIN ((dbo_Uitvoering AS U 
INNER JOIN dbo_Stoel AS S ON U.Zaal = S.Zaal) 

LEFT JOIN (
  SELECT dbo_Bezetting.Stoelnummer 
  FROM dbo_Uitvoering INNER JOIN dbo_Bezetting ON (dbo_Uitvoering.Uitvoeringsnummer = dbo_Bezetting.Uitvoeringsnummer) AND (dbo_Uitvoering.Voorstellingsnummer = dbo_Bezetting.Voorstellingsnummer) 
  WHERE dbo_Bezetting.Voorstellingsnummer=Forms!frmKassaVerkoop!lstVoorstelling And dbo_Bezetting.Uitvoeringsnummer=Forms!frmKassaVerkoop!lstUitvoering
)  
AS BS ON S.Stoelnummer = BS.Stoelnummer) ON dbo_Prijzen.Voorstellingsnummer = U.Voorstellingsnummer AND dbo_Prijzen.Rang = S.Rang

LEFT JOIN (
  SELECT dbo_Bezetting.Stoelnummer 
  FROM dbo_Uitvoering 
  INNER JOIN dbo_Bezetting ON (dbo_Uitvoering.Uitvoeringsnummer = dbo_Bezetting.Uitvoeringsnummer) AND (dbo_Uitvoering.Voorstellingsnummer = dbo_Bezetting.Voorstellingsnummer) 
  WHERE dbo_Bezetting.Voorstellingsnummer=Forms!frmKassaVerkoop!lstVoorstelling And dbo_Bezetting.Uitvoeringsnummer=Forms!frmKassaVerkoop!lstUitvoering AND dbo_Bezetting.Reservering = Forms!frmKassaVerkoop!Reserveringsnummer
)  
AS PS ON S.Stoelnummer = PS.Stoelnummer) ON dbo_Prijzen.Voorstellingsnummer = U.Voorstellingsnummer AND dbo_Prijzen.Rang = S.Rang

WHERE U.Voorstellingsnummer=[Forms]![frmKassaVerkoop]![lstVoorstelling] AND U.Uitvoeringsnummer=[Forms]![frmKassaVerkoop]![lstUitvoering];

Yes, it's quite the beast. I can cut out line 13-19 and it works, I can cut out line 6-11 and it works.
The closing parenthesis on line 19 is in excess but I do think it should be placed there, I just can't for the life of me figure out where the starting parenthesis should be placed.

The closest I found to an example is:


http://nm1m.blogspot.com/2007/10/mul...ms-access.html
but I can't seem to figure out how this logic should be applied to my own query.

[NTC]

why don't you create the query using design grid?

you can then select it to change to sql view and it gives you the syntax....

[Axeia]

If the design view and this grid you speak of are the same thing, it's because it doesn't give a very useful representation of this query. No idea how'd I go about adding the second the left join to the attached screenshot from there, is that even possible?

[orange]

Do you have your relationships set up? If so, I think NTC is suggesting that you bring in the tables you need, and create the select query using the wizard.

Then go to the SQL view and look at the SQL that Access has produced based on your selection of tables and fields.

[Axeia]

The relations are set up, but not between the tables I'm joining. It's pretty much an accident waiting to happen as the input form is supposed to prevent erroneous input
(just doing what I'm asked to do, data validation in the middlelayer isn't how I'd do it myself)

If only I could figure out the logic behind these parenthesis, worked around it in another suboptimal way for now.

[khalid]

Count the parenthesis for each Starting ( and Ending ) one... if you are using nested AND, OR if conditions then try to pair them.

[Axeia]

I tried many combinations and couldn't figure it out.
However! As I have a working SQL Server query and I'm using linked tables I just created a view out of the query and then linked to the view.

Can't believe I didn't think of that sooner, much less of a headache writing my SQL in SQL Server as I'm familiar with it.

[khalid]

if you create you query with access grid view and then copy the SQL of the query and paste it in the immediate window of VBA, then drill down for each line by pressing Enter Key:

SELECT column1, column2,...
FROM table1
INNER JOIN
......
Where.....

That would be easy for you to trap your Long SQL Statement in manageable way.

[orange]

Glad you have something working. I just did a bracket matching of your supplied code using TextPad.

There is no matching bracket for line 18 )

You could try removing it and see if your "posted code" works.

[Axeia]

Glad you have something working. I just did a bracket matching of your supplied code using TextPad.

There is no matching bracket for line 18 )

You could try removing it and see if your "posted code" works.

Actually, it's on line 19 and I mentioned it in the opening post

The closing parenthesis on line 19 is in excess but I do think it should be placed there, I just can't for the life of me figure out where the starting parenthesis should be placed.

Thanks for trying though, I more or less did the same thing but used KWrite instead of TextPad, let's hope access 2011 will do it itself.

Anyway I fixed it by just throwing the logic in SQL Server, I learned my lesson. I simply shouldn't try do something semi-advanced in Access itself when I can do it in SQL Server. I'm using linked tables anyway, so adding a few linked views is just a minor hassle.