SQL syntax error in VBA (1 Viewer)

[sphynx]

Hi pulling my hair out again!!!

I cannot find the syntax error in my SQL and was wondering whether anyone could advise what I am missing. Please bear with me as I am still learning

SQL1 = "SELECT MasterStaffList_Tbl.FULLNAME, MasterStaffList_Tbl. [START DATE], MasterStaffList_Tbl.UNIT, " & _
"MasterStaffList_Tbl.SHIFT, MasterStaffList_Tbl.JOB, MasterStaffList_Tbl.DEPARTMENT " & _
"FROM MasterStaffList_Tbl, StaffLookupCrossover_Qry " & _
"WHERE (((MasterStaffList_Tbl.ACTIVE) = Yes) And ((StaffLookupCrossover_Qry.[SHIFT ID]) = [SHIFT]) " & _
"And ((StaffLookupCrossover_Qry.[DEPARTMENT ID]) = [DEPARTMENT]) " & _
"And ((StaffLookupCrossover_Qry.[JOB ID]) = [JOB]) And ((StaffLookupCrossover_Qry.[UNIT ID]) = [UNIT])) " & _
"GROUP BY MasterStaffList_Tbl.FULLNAME, MasterStaffList_Tbl.[START DATE], MasterStaffList_Tbl.UNIT, " & _
"MasterStaffList_Tbl.SHIFT, MasterStaffList_Tbl.JOB, MasterStaffList_Tbl.DEPARTMENT; "

Any help would be greatley received

 

[namliam]

The only problem I can find, quickly..., is the extra space here:
MasterStaffList_Tbl. [START DATE],

Behind the "tbl."

 

[DCrake]

You are missing the join between the tbl and the qry. However, you have not said what error you are getting!!

 

[namliam]

The join in this case is located in the where... however a proper join would be better.

 

[sphynx]

namliam - Thanks for spotting that it seems to have sorted out the issue I was having.

With regards to the the Join, as I am just dipping my big toe into the sea of SQL I have been generating the basis of all my SQL via the query builder and then pasting into VB & reformatting.

I am slowly coming to terms with the construction and syntax, to speed up my learning are there any reference that you would recommend?

DCrake - Again, thank you for your input. So my future questions are clearer I am assuming that by your reference to my error you mean the runtime error code?

 

[namliam]

If you are posting SQL from the query builder then you are not using the GUI to join the tables??

You can join the tables by "click and dragging" fields from one table to the other. This will enforce the joins that you now have in your where.... Try it and you will love it.