Missing Operator performing multiple inner joins

I'm building an sql statement through code in visual basic for an ms access database. The statement is created as a user selects fields from various tables. It works fine for 2 tables, but once a third table is introduced I get the following error:

Run-time error '-2147217900 (80040e14)
Syntax error (missing operator) in query expression

Here is the code that it is executing when it happens:

SELECT Tbl_Members.MemberID, Tbl_Member_History.Initiated_Date, Tbl_Partner.Name FROM Tbl_Members Inner Join Tbl_Member_History ON (Tbl_Members.MemberID = Tbl_Member_History.MemberID) Inner Join Tbl_Partner ON (Tbl_Member_History.MemberID = Tbl_Partner.MemberID)

It does not matter what fields or tables, as soon as there is more than 2 tables selected it gives the error. Also, as far as syntax, I've tried with and without the parenthesis.

Can anyone tell me what is wrong? Thanks.
fisherbrsnch00Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

alexgudCommented:
Try this one

SELECT Tbl_Members.MemberID, Tbl_Member_History.Initiated_Date, Tbl_Partner.Name FROM (Tbl_Members Inner Join Tbl_Member_History ON Tbl_Members.MemberID = Tbl_Member_History.MemberID) Inner Join Tbl_Partner ON Tbl_Member_History.MemberID = Tbl_Partner.MemberID;
0
alexgudCommented:
Access is not smart enough :) and very sensitive :)
0
GreymanMSCCommented:
Yeap.  Access SQL requires nested joins to be properly embraced.  The parenthesis go around the entire nested join, not just the join criteria (although that's okay too, just not required and is a seperate issue).

  Buid your joins like so:   (T1 join T2 on T1.F1=T2.F1) join T3 on T1.F2=T3.F2


It helps on complex joins to use aliasing, to reduce typing and improve legibility.  It's a little easier to eyeball a query if the tablenames are shortened through aliasing.

   SELECT M.MemberID, MH.Initiated_Date, P.Name
   FROM (Tbl_Members AS M Inner Join Tbl_Member_History AS MH
   ON M.MemberID = MH.MemberID) Inner Join Tbl_Partner AS P
   ON MH.MemberID = P.MemberID;
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

fisherbrsnch00Author Commented:
Yes that works, but when I choose a fourth table, same error. I'm not all that familiar with joins in SQL. Where do the parenthesis come after the above example? This is the code from the fourth table query:

SELECT Tbl_Members.MemberID, Tbl_Member_History.Initiated_Date, Tbl_Partner.Name, Tbl_Member_History.LodgeID, Tbl_Home_Phone.Home_Phone_Number FROM (Tbl_Members Inner Join Tbl_Member_History ON (Tbl_Members.MemberID = Tbl_Member_History.MemberID)) Inner Join Tbl_Partner ON (Tbl_Member_History.MemberID = Tbl_Partner.MemberID) Inner Join Tbl_Home_Phone ON (Tbl_Partner.MemberID = Tbl_Home_Phone.MemberID)

I'm having a little trouble because I'm doing it through vb. Basically, assuming that there are many tables in the query, what part of the sql statement needs parenthesis? I see that it requires it from the point... "FROM (".. but what about the rest of the statement where there is no "FROM", does it go around "inner Join Table Name...". I know that sounds confusing. If it does not make sense I will try and explain it more. I know this is not a vb topic area, but here is the code I am using to try and build the statement:

If UBound(wizard_tables) > 1 Then
    SQL_Statement = SQL_Statement & " FROM ("
Else
    SQL_Statement = SQL_Statement & " FROM "
End If

For i = 0 To UBound(wizard_tables) - 1
    SQL_Statement = SQL_Statement & wizard_tables(i)
    If UBound(wizard_tables) > 1 Then
        If i > 0 Then
            SQL_Statement = SQL_Statement & " ON (" & wizard_tables(i - 1) & ".MemberID"
            SQL_Statement = SQL_Statement & " = " & wizard_tables(i) & ".MemberID)"
        End If
        If i = 1 Then
            SQL_Statement = SQL_Statement & ")"
        End If
        If i < UBound(wizard_tables) - 1 Then
            SQL_Statement = SQL_Statement & " Inner Join "
        End If
    End If
Next i

What happens is that as the tables and fields are selected from a list, they populate an array, the above code is for the table portion, which is the only part giving me problems.

If anyone has a way to improve the code, or a different way to do it I would appreciate the info.
0
alexgudCommented:
SELECT Tbl_Members.MemberID, Tbl_Member_History.Initiated_Date, Tbl_Partner.Name, Tbl_Member_History.LodgeID, Tbl_Home_Phone.Home_Phone_Number FROM (Tbl_Members Inner Join Tbl_Member_History ON Tbl_Members.MemberID = Tbl_Member_History.MemberID) (Inner Join Tbl_Partner ON Tbl_Member_History.MemberID = Tbl_Partner.MemberID)(Inner Join Tbl_Home_Phone ON Tbl_Partner.MemberID = Tbl_Home_Phone.MemberID)


For i = 0 To UBound(wizard_tables) - 1
    SQL_Statement = SQL_Statement & "(" & wizard_tables(i)
    If UBound(wizard_tables) > 1 Then
        If i > 0 Then
            SQL_Statement = SQL_Statement & " ON " & wizard_tables(i - 1) & ".MemberID"
            SQL_Statement = SQL_Statement & " = " & wizard_tables(i) & ".MemberID"
        End If
        If i = 1 Then
            SQL_Statement = SQL_Statement & ")"
        End If
        If i < UBound(wizard_tables) - 1 Then
            SQL_Statement = SQL_Statement & " Inner Join "
        End If
    End If
Next i
0
GreymanMSCCommented:
Q: I'm having a little trouble because I'm doing it through vb. Basically, assuming that there are many tables in the query, what part of the sql statement needs parenthesis?
---

Put parentesis around the entire nested join.  Like so:  (A join B on A.id=B.id)

Everytime you add another table, add another layer of parenthesis.

((((A join B on A.a=B.a) join C on B.b=C.b) join D on C.c=D.c) join E on D.d=E.d) ...

A little more obscure, but equally valid is to nest the other way.

(E join (D join (C join (B join A on A.a=B.a) on B.b=C.b) on C.c=D.c) on D.d=E.d) ...
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
fisherbrsnch00Author Commented:
I'm about ready to give up, I can't figure it out. I finally got the VB code to get all the correct parenthesis in place and successfully join three tables but on the fourth it fails. I've been trying for so many hours, I just don't know what to do. Does anyone have a simple vb routine that will loop through an array of tables (more than 2 or 3, it will vary) and join them? All tables that I have are linked by MemberID. This is really important and everytime I think I have it I get that missing operator error.
This is the SQL statement that it keeps failing on and I've tried just about every combo of parenthesis that I can think of:
SELECT Tbl_Members.MemberID, Tbl_Member_History.Initiated_Date, Tbl_Partner.Name, Tbl_Children.Name FROM (Tbl_Members Inner Join Tbl_Member_History ON Tbl_Members.MemberID = Tbl_Member_History.MemberID) (Inner Join Tbl_Partner ON Tbl_Member_History.MemberID = Tbl_Partner.MemberID) Inner Join Tbl_Children ON Tbl_Partner.MemberID = Tbl_Children.MemberID
0
fisherbrsnch00Author Commented:
Okay I figured it out. My problem was that I did not understand exactly where the parenthesis should be. This is the four tables:

SQL_Statement = "SELECT Tbl_Members.MemberID, Tbl_Member_History.Initiated_Date, Tbl_Partner.Name, Tbl_Home_Phone.Home_Phone_Description FROM ((Tbl_Members INNER JOIN Tbl_Member_History ON Tbl_Members.MemberID = Tbl_Member_History.MemberID) INNER JOIN Tbl_Partner ON Tbl_Member_History.MemberID = Tbl_Partner.MemberID) INNER JOIN Tbl_Home_Phone ON Tbl_Partner.MemberID = Tbl_Home_Phone.MemberID;"

So now I understand that there needs to be parenthesis opened around all inner joins except for the last one. Thanks for everone who helped.
0
GreymanMSCCommented:
Glad to have helped.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.