CODEWITHSUNDEEP
sqlvba
Rob
Rob

Reputation: 69

VBA sql string error: missing operator

Writing an sql string in vba and getting syntax error for a missing operator. I thought the error was with the inner joins so I tried taking them out but still same error.

Here is the query string:

sql1 = "SELECT WorkOrder.ProjectID, tref_dep.department, live_project.project_codename " _
     & "FROM WorkOrder " _
     & "INNER JOIN tlive_project " _
     & "ON tlive_project.project_id = WorkOrder.ProjectID " _
     & "INNER JOIN tref_dep " _
     & "ON tref_dep.dep_id = WorkOrder.ToDepartment " _
     & "WHERE WorkOrder.ToDepartment = " & rs1!wo_depart_id & " AND WorkOrder.ProjectID = " & rs1!proj_id _
     & " CONTAINS(WorkOrder.WorkOrderDescription, 'TimeForce Upload,') " _
     & "LIMIT 1"

I am probably missing something simple but any help is greatly appreciated!

Upvotes: 0

Views: 142

Answers (2)

Rob
Rob

Reputation: 69

VBA requires parenthesis for 2 or more JOIN clauses. 

sql1 = "SELECT WorkOrder.ProjectID, tref_dep.department, live_project.project_codename " _
 & "FROM (WorkOrder " _
 & "INNER JOIN tlive_project " _
 & "ON tlive_project.project_id = WorkOrder.ProjectID) " _
 & "INNER JOIN tref_dep " _
 & "ON tref_dep.dep_id = WorkOrder.ToDepartment " _
 & "WHERE WorkOrder.ToDepartment = " & rs1!wo_depart_id & " AND WorkOrder.ProjectID = " & rs1!proj_id _
 & " CONTAINS(WorkOrder.WorkOrderDescription, 'TimeForce Upload,') " _
 & "LIMIT 1"

Upvotes: 0

potashin
potashin

Reputation: 44581

You have forgotten a logic operator before CONTAINS(..) :

 & "WHERE WorkOrder.ToDepartment = " & rs1!wo_depart_id & " AND WorkOrder.ProjectID = " & rs1!proj_id _
 & "AND CONTAINS(WorkOrder.WorkOrderDescription, 'TimeForce Upload,') " _
 & "LIMIT 1"

Upvotes: 1

Related Questions