VBA sql string error: missing operator

Question

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!

souslov · Accepted Answer · 2014-05-12 19:57:50Z

1

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"

edited May 12, 2014 at 19:57

answered May 12, 2014 at 19:50

souslov

44.7k11 gold badges92 silver badges113 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Rob Over a year ago

Thanks @notulysses for the quick reply. I should have been more specific in my post but, rs1!wo_depart_id and rs1!proj_id are integers so I don't think I need the single quotes around them.

souslov Over a year ago

@Rob : have you succeeded?

Rob Over a year ago

Thanks for your help. Found out in VBA if you have 2 or more JOIN clauses you have to put a parenthesis around each individual clause. That was where I was getting my error. I have updated with answer

Rob · Accepted Answer · 2014-05-14 18:52:19Z

0

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"

answered May 14, 2014 at 18:52

Rob

691 silver badge9 bronze badges

Collectives™ on Stack Overflow

VBA sql string error: missing operator

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related