VBA - Missing Operator SQL Query Error

Question

I'm trying to update the table with inner join using VBA in MS Access but I've tested the SQL Update statement in SQL Server Management Studio and it's working. I'm getting an error as a result, see below.

Appreciate any help.

SQL = "Update A set A.RevBillCtrl = 8 from dbo_tblMain A inner join dbo_tblPlateNo as B ON B.PNC = A.PLC inner join dbo_tblSubcons as C on B.SCC = C.SCC "

What data type is A.RevBillCtrl?

Ken White
– Ken White

2017-03-02 03:21:47 +00:00
Commented Mar 2, 2017 at 3:21 — Ken White
– Ken White, Commented Mar 2, 2017 at 3:21
Data type of A.RevBillCtrl is integer.

RedHat
– RedHat

2017-03-02 03:26:22 +00:00
Commented Mar 2, 2017 at 3:26 — RedHat
– RedHat, Commented Mar 2, 2017 at 3:26

Mathieu Guindon · Accepted Answer · 2017-03-02 04:30:10Z

1

In Access the Update with joins has a different syntax comparing to SQL Server. The correct syntax will be:

Update dbo_tblMain AS A 
inner join dbo_tblPlateNo as B on B.PNC = A.PLC 
inner join dbo_tblSubcons as C on B.SCC = C.SCC
set A.RevBillCtrl = 8

BTW, earlier Access versions did not like the INNER JOINS that follow one after another. They used a nested joins syntax:

Update dbo_tblMain AS A 
inner join (dbo_tblPlateNo as B
  inner join (dbo_tblSubcons as C 
  on B.SCC = C.SCC)
on B.PNC = A.PLC)
set A.RevBillCtrl = 8

edited Mar 2, 2017 at 4:30

Mathieu Guindon

71.5k8 gold badges113 silver badges250 bronze badges

answered Mar 2, 2017 at 3:33

cha

10.4k1 gold badge20 silver badges27 bronze badges

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

6 Comments

RedHat Over a year ago

I have tried below query but i'm getting an error. RunTime Error '3075' : Syntax Error (missing operator) in query expression 'B.PNC = A.PLC inner join dbo_tblSubcons AS C ON B.SCC = C.SC'. Update dbo_tblMain A inner join dbo_tblPlateNo as B ON B.PNC = A.PLC inner join dbo_tblSubcons as C on B.SCC = C.SCC set A.RevBillCtrl = 8

cha Over a year ago

try one with the nested joins

cha Over a year ago

sorry, messed the nested joins syntax. try again

Mathieu Guindon Over a year ago

The bottom block was the same as the top one, so I edited the bottom one to a nested join, but then realized there were parentheses.. and I don't know whether parentheses are mandatory in Access, nor where they're supposed to be going - so I edited it further, but I think I should have just left it alone... feel free to rollback or fix or whatever.

cha Over a year ago

@Mat'sMug: yes, Access's craziness around their joins makes everyone crazy

|

r.paul Ǿ · Accepted Answer · 2017-03-03 03:41:56Z

0

The idea basically is that you have to do the JOINs in a nested way. See below correct one.

Sql = "UPDATE dbo_tblMain
      INNER JOIN (dbo_tblPlateNo 
       INNER JOIN dbo_tblSubCons 
       ON (dbo_tblPlateNo.SCC = dbo_tblSubCons.SCC)) 
      ON (dbo_tblMain.PLC = dbo_tblPlateNo.PNC) 
      SET dbo_tblMain.RevBillCtrl = 8"

edited Mar 3, 2017 at 3:41

answered Mar 3, 2017 at 3:35

r.paul Ǿ

1111 gold badge2 silver badges11 bronze badges

1 Comment

O. Gungor Over a year ago

Test your queries by writing them as an actual query not in vba. Not sure u need vba here. If you just use query desinger to drag the joins, access will handle the syntax

Collectives™ on Stack Overflow

VBA - Missing Operator SQL Query Error

2 Answers 2

6 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

6 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related