I get the error "Syntax Error (missing operator) in query expression"

sql = "SELECT Chores.ChoresID, Chores.ChoresDate, Materials.MaterialName, Advisers.AdviserInit, Advisers.AdviserSurname," _
& "Area.Area, Chores.StudentNo, Student.Forename" _
& "FROM Chores" _
& "INNER JOIN Materials on Chores.MaterialID = Materials.MaterialID" _
& "INNER JOIN Advisers on Chores.AdviserID = Advisers.AdviserID" _
& "INNER JOIN Area on Chores.AreaCode = Area.AreaCode" _
& "INNER JOIN Student on Student.StudentNo =  Chores.StudentNo"
 Adodc1.CommandType = adCmdText
   Adodc1.RecordSource = sql
   Adodc1.Refresh

   Set DataGrid1.DataSource = Adodc1

In my opinion exception has been raised for shortage of "Space" in SQL Statement.
It should be

sql = "SELECT Chores.ChoresID, Chores.ChoresDate, Materials.MaterialName, Advisers.AdviserInit, Advisers.AdviserSurname, " _
& "Area.Area, Chores.StudentNo, Student.Forename " _
& "FROM Chores " _
& "INNER JOIN Materials on Materials.MaterialID = Chores.MaterialID " _
& "INNER JOIN Advisers on Advisers.AdviserID = Chores.AdviserID " _
& "INNER JOIN Area on Area.AreaCode = Chores.AreaCode " _
& "INNER JOIN Student on Student.StudentNo =  Chores.StudentNo"

Did you use the SQL Statement that I post.?
Please, write a "," in between two "Inner Join" Clause.
It should be like

& "INNER JOIN Materials on Materials.MaterialID = Chores.MaterialID, " _
& "INNER JOIN Advisers on Advisers.AdviserID = Chores.AdviserID, " _
& "INNER JOIN Area on Area.AreaCode = Chores.AreaCode, " _
& "INNER JOIN Student on Student.StudentNo =  Chores.StudentNo"

You can write SQL Statement as

sql = "SELECT A.ChoresID, A.ChoresDate, A.MaterialID, A.AdviserID, A.AreaCode, B.MaterialName, C.AdviserInit, C.AdviserSurname, " _
& "D.Area, A.StudentNo, E.Forename From Chores A, " _
& "(Select MaterialID, MaterialName From Materials Group By MaterialID) B " _
& "Where B.MaterialID = A.MaterialID, " _
& "(Select AdviserID, AdviserInit, AdviserSurname From Advisers Group By AdviserID) C, " _
& "Where C.AdviserID = A.AdviserID, " _
& "(Select AreaCode, Area From Area Group By AreaCode) D " _
& "Where D.AreaCode = A.AreaCode, " _
& "(Select StudentNo, Forename From Student Group By StudentNo) E " _
& "Where E.StudentNo = A.StudentNo Order By A.ChoresID"

Edited 2 Years Ago by Shark_1

This question has already been answered. Start a new discussion instead.