Syntax error (missing operator) in query expression

[Bile]

-->>Hello I hope all is well.
-->>I'm trying to join or select some data from 5 tables by using the following join statement:

SQL ="SELECT RegistrationTB.Christian_Name,RegistrationTB.Father_Name,RegistrationTB.Birth_Date,"

              "BaptismTB.Date_Baptised,BaptismTB.Parish_Name,BaptismTB.Minister_Name," & _
              "CommunionTB.Date_Communised,CommunionTB.Parish_Name," & _
              "ConfirmationTB.LC_No,ConfirmationTB.Date_Confirmation,ConfirmationTB.Parish_Name,ConfirmationTB.Minister_Name," & _
              "MarriageTB.LM_No,MarriageTB.Date_Married,MarriageTB.Parish_Name,MarriageTB.Mr_Name,MarriageTB.Mrs_Name,MarriageTB.Certificate_No,MarriageTB.Minister_Name " & _
              "FROM RegistrationTB " & _
              "INNER JOIN BaptismTB  ON RegistrationTB.LB_No=BaptismTB.LB_No " & _
              "INNER JOIN CommunionTB  ON BaptismTB.LB_No=CommunionTB.LB_No " & _
              "INNER JOIN ConfirmationTB ON BaptismTB.LB_No=ConfirmationTB.LB_No " & _
              "INNER JOIN MarriageTB ON BaptismTB.LB_No=MarriageTB.LB_No " & _
              "WHERE ((LB_No) = '" & txtLB_No.Text & "');"

-->>But what I get is a Syntax error (missing operator) in query expression.
-->>Please help.

[AndreRet]

Firstly, is LB_NO an integer (number only)? If so, remove the ' signs from your sql....

"WHERE ((LB_No) = " & txtLB_No.Text & ");"

like this.

Secondly, your WHERE clause is in the incorrect place, should come after FROM RegistrationTB -

"FROM RegistrationTB WHERE LB_No = '" & txtLB_No.Text & "')" & _

Thirdly, you are missing a &_ after the first line...

SQL ="SELECT RegistrationTB.Christian_Name,RegistrationTB.Father_Name,RegistrationTB.Birth_Date," &_

Just read it quickly, if more errors, let me know.

[Bile]

-->>Should it be something like this:

SQL = "SELECT RegistrationTB.LB_No,RegistrationTB.Christian_Name,RegistrationTB.Father_Name,RegistrationTB.Birth_Date," & _
              "BaptismTB.LB_No,BaptismTB.Date_Baptised,BaptismTB.Parish_Name,BaptismTB.Minister_Name," & _
              "CommunionTB.LB_No,CommunionTB.Date_Communised,CommunionTB.Parish_Name," & _
              "ConfirmationTB.LC_No,ConfirmationTB.Date_Confirmation,ConfirmationTB.Parish_Name,ConfirmationTB.Minister_Name," & _
              "MarriageTB.LB_No,MarriageTB.LM_No,MarriageTB.Date_Married,MarriageTB.Parish_Name,MarriageTB.Mr_Name,MarriageTB.Mrs_Name,MarriageTB.Certificate_No,MarriageTB.Minister_Name " & _
              "FROM RegistrationTB WHERE LB_No = " & txtLB_No.Text & ")" & _
              " INNER JOIN BaptismTB ON (BaptismTB.LB_No=RegistrationTB.LB_No)" & _
              " INNER JOIN CommunionTB ON (CommunionTB.LB_No=BaptismTB.LB_No)" & _
              " INNER JOIN ConfirmationTB ON (ConfirmationTB.LB_No=BaptismTB.LB_No)" & _
              " INNER JOIN MarriageTB ON (MarriageTB.LB_No=BaptismTB.LB_No);"

-->>If so the error is still there!!!

[AndreRet]

I'll run the code some time tonight if time allows. :) Will post a solution tomorrow morning.

[Bile]

-->>Thanks for your concern.

[BitBlt]

No, the "Where" clause comes AFTER all the "JOIN" clauses.

Your error is probably due to do with the fact that LB_No in the "where" clause is not fully qualified. It could be in any of the 5 tables, so you need to specify which one.

Just a side note...putting in a few spaces would improve readability immensely.

Hope this helps. Good luck!

[AndreRet]

Indeed, after WHERE, my bad, thanx.

[Bile]

-->>I think I've just decided to ignore the Join issue here and I've decided to use the following statements:

SQL = "SELECT RegistrationTB.LB_No, RegistrationTB.Christian_Name, RegistrationTB.Father_Name, RegistrationTB.Birth_Date," & _
              "BaptismTB.LB_No, BaptismTB.Date_Baptised, BaptismTB.Parish_Name, BaptismTB.Minister_Name, " & _
              "CommunionTB.LB_No, CommunionTB.Date_Communised, CommunionTB.Parish_Name, " & _
              "ConfirmationTB.LB_No,ConfirmationTB.LC_No, ConfirmationTB.Date_Confirmation, ConfirmationTB.Parish_Name, ConfirmationTB.Minister_Name, " & _
              "MarriageTB.LB_No, MarriageTB.LM_No, MarriageTB.Date_Married, MarriageTB.Parish_Name, MarriageTB.Mr_Name, MarriageTB.Mrs_Name, MarriageTB.Certificate_No, MarriageTB.Minister_Name " & _
              "FROM RegistrationTB,BaptismTB,CommunionTB,ConfirmationTB,MarriageTB " & _
              "WHERE RegistrationTB.LB_No = '" & txtLB_No.Text & "';"
RS.Open SQL, Conn, adOpenDynamic

-->>And the velues to be returned on my interface I've used the following:

Do While Not RS.EOF
        If RS.Fields("RegistrationTB.LB_No") = Me.txtLB_No.Text Or RS.Fields("BaptismTB.LB_No") = Me.txtLB_No.Text Or RS.Fields("CommunionTB.LB_No") = Me.txtLB_No.Text Or RS.Fields("ConfirmationTB.LB_No") = Me.txtLB_No.Text Or RS.Fields("MarriageTB.LB_No") = Me.txtLB_No.Text Then

          If RS.Fields("RegistrationTB.LB_No") = Me.txtLB_No.Text Then
          fmMembe_Report.lblName.Caption = RS.Fields("Christian_Name") & "  " & RS.Fields("Father_Name")
          fmMembe_Report.lblBirth_Date.Caption = RS.Fields("Birth_Date")
          End If

          If RS.Fields("BaptismTB.LB_No") = Me.txtLB_No.Text Then
          fmMembe_Report.lblLB_No.Caption = txtLB_No.Text
          fmMembe_Report.lblBap_Date.Caption = RS.Fields("Date_Baptised")
          fmMembe_Report.lblBap_Parish.Caption = RS.Fields("BaptismTB.Parish_Name")
          fmMembe_Report.lblBap_Minister.Caption = RS.Fields("BaptismTB.Minister_Name")
          End If

          If RS.Fields("CommunionTB.LB_No") = Me.txtLB_No.Text Then
          fmMembe_Report.lblComm_Date.Caption = RS.Fields("Date_Communised")
          fmMembe_Report.lblComm_Parish.Caption = RS.Fields("CommunionTB.Parish_Name")
          End If

          If RS.Fields("ConfirmationTB.LB_No") = Me.txtLB_No.Text Then
          fmMembe_Report.lblLC_No.Caption = RS.Fields("LC_No")
          fmMembe_Report.lblConf_Date.Caption = RS.Fields("Date_Confirmation")
          fmMembe_Report.lblConf_Parish.Caption = RS.Fields("ConfirmationTB.Parish_Name")
          fmMembe_Report.lblConf_Minister.Caption = RS.Fields("ConfirmationTB.Minister_Name")
          End If

          If RS.Fields("MarriageTB.LB_No") = Me.txtLB_No.Text Then
          fmMembe_Report.lblLM_No.Caption = RS.Fields("LM_No")
          fmMembe_Report.lblMarr_Date.Caption = RS.Fields("Date_Married")
          fmMembe_Report.lblMarr_Parish.Caption = RS.Fields("MarriageTB.Parish_Name")
          fmMembe_Report.lblHusband.Caption = RS.Fields("Mr_Name")
          fmMembe_Report.lblWife.Caption = RS.Fields("Mrs_Name")
          fmMembe_Report.lblMarr_Certificate.Caption = RS.Fields("Certificate_No")
          fmMembe_Report.lblMarr_Minister.Caption = RS.Fields("MarriageTB.Minister_Name")
          End If

        End If
           RS.MoveNext
        Loop

-->>It did the same thing I wanted,though I thought that the best way was to use the join statement.
-->>Though I have what I wanted but anny advice will be useful to me if its not a proffesional way to do that and also if the join is clear to anny one I'll be glad to see it.
-->>Thank you all.

[BitBlt]

Caution! By including every table and not joining them, you are creating a 5-dimensional Cartesian product. The volume of data returned will exponentially explode as you put more data in your tables.

If you included that the LB_no for each table is equal to your text box value as part of the "where" clause, at least that would limit the damage, but it's not a really good way to do it. USE THE JOINS.

This snippet should do the trick:

"FROM RegistrationTB" & _
" INNER JOIN BaptismTB ON BaptismTB.LB_No = RegistrationTB.LB_No" & _
" INNER JOIN CommunionTB ON CommunionTB.LB_No = RegistrationTB.LB_No" & _
" INNER JOIN ConfirmationTB ON ConfirmationTB.LB_No = RegistrationTB.LB_No" & _
" INNER JOIN MarriageTB ON MarriageTB.LB_No = RegistrationTB.LB_No
" WHERE RegistrationTB.LB_No = " & txtLB_No.Text & ";"

Note that this assumes there is a corresponding row in every table for every LB_No. If not, you should be using LEFT JOIN instead of INNER JOIN.

Hope that helps somewhat. Good luck!

[Bile]

-->>Now I'm having a doubt on the concequencies,I tried to sort it out but I failde may be if you help me out BitBit on how exlactly the statement shoul be.
-->>For sure I need the Values even if they just exist in only one table so the LEFT JOIN is good I gues.
-->>I tried it but failed and the Error is as to attached file.
-->>I hope all the tables and fields are as in my post and so the statement is what Im suffering for.

[BitBlt]

I need to see your entire statement, and I need to know what the relationships are between your tables. The snippet above should be enough (with a correction to line 5 where I missed a line-continuation character).

[Bile]

-->>Now considering your caution (BitBit)... I'v been troubled now let me show up some details...
-->>I'v got 5 Tables,namely RegistrationTB,BaptismTB,CommunionTB,ConfirmationTB,MarriageTB
-->>RegistrationTB takes in new members...
-->>BaptismTB takes only members who are alredy registered in RegistrationTB...(Their relationship:"Only include rows where the joined fields from both tables are equal")
-->>CommunionTB takes only members who are alredy in BaptismTB...(Their relationship as well is:"Only include rows where the joined fields from both tables are equal")
-->>ConfirmationTB takes only members who are alredy in BaptismTB...(Their relationship as well is:"Only include rows where the joined fields from both tables are equal")
-->>MarriageTB takes only members who are alredy in BaptismTB...(Their relationship as well is:"Only include rows where the joined fields from both tables are equal")
-->>Also I have Checked the "Enforce Inferential intergrity,Cascade Update related fields,Cascade delete related records."
-->>I hope that now I explained my self well so can you help mw with the Join statement please to avoid the incoming BOMB!!!
-->>Tanx.