Pulling data using SQL and ADODB Error

[JOCoyle]

Hello,

Firstly I'm sorry if this is in the wrong thread but I wasn't sure which one to choose because the problem is to do with Excel, Access and SQL.

I am having a problem getting an SQL query to work at all.

The code for the SQL string is as follows:

   strSQL = "SELECT Staff_Roster.[User_ID], Staff_Roster.[Full_Name], Staff_Roster.[Zone], Staff_Roster.[Team], " & _
                    "Feedback_Scores_Advisor.[Score], Feedback_Data.[IssuesIdentified], Feedback_Data.[Issue], Feedback_Data.[Type] " & _
                "FROM Staff_Roster " & _
                    "INNER JOIN Feedback_Scores_Advisor " & _
                        "ON Staff_Roster.[User_ID] = Feedback_Scores_Advisor.[UserID] " & _
                    "INNER JOIN Feedback_Data " & _
                        "ON Staff_Roster.[User_ID] = Feedback_Data.[UserID] " & _
                    "WHERE Feedback_Scores_Advisor.[FirstDay] = " & FirstDay & " " & _
                        "AND Feedback_Data.[FDate] >= " & FirstDay & ";"

I'm not sure why it's not working but essentially it's giving me a Syntax Error (missing operator) in query expression error.

Hopefully the problem should be quite self-explanatory but if it doesn't make sense please ask and I will elaborate on the setup.

The relationships within the database tables are as follows (the ones appropriate to the problem):

• Feedback_Data.UserID >---< Staff_Roster.User_ID
• Feedback_Scores_Advisor.UserID >---< Staff_Roster.User_ID
• (Both many to many)

Thank you in advance,

J Coyle.

 

[JOCoyle]

Ok,

I found the problem..

Adding parenthesis from the from to the end of the first inner join like so works:

strSQL = "SELECT Staff_Roster.[User_ID], Staff_Roster.[Full_Name], Staff_Roster.[Zone], Staff_Roster.[Team], " & _
                    "Feedback_Scores_Advisor.[Score], Feedback_Data.[IssuesIdentified], Feedback_Data.[Issue], Feedback_Data.[Type] " & _
                "FROM (Staff_Roster " & _
                    "INNER JOIN Feedback_Scores_Advisor " & _
                        "ON Staff_Roster.[User_ID] = Feedback_Scores_Advisor.[UserID]) " & _
                    "INNER JOIN Feedback_Data " & _
                        "ON Staff_Roster.[User_ID] = Feedback_Data.[UserID] " & _
                    "WHERE Feedback_Scores_Advisor.[FirstDay] = " & FirstDay & " " & _
                        "AND Feedback_Data.[FDate] >= " & FirstDay & ";"

Hope this helps someone, somewhere..
J Coyle