Can't figure out this syntax error VBA SQL

Question

I'm having an issue with Access. I'm trying to update one table (shipmentdata) with a few values from another table (customers). This is my code right now:

Option Compare Database

Sub Update()

Dim strSQL As String

strSQL = "UPDATE ShipmentData As A " & _
         "SET A.[Sales Rep] = B.[Sales Rep], A.OfficeNbr = B.OfficeNbr " & _
         "FROM A " & _
         "INNER JOIN Customers As B " & _
         "ON A.Owner = B.Name;"

DoCmd.RunSQL strSQL

End Sub

I keep getting an error: "Run-time error '3075': Syntax error (missing operator) in query expression 'B.OfficeNbr FROM A INNER JOIN Customers As B ON A.Owner = B.Name'.

I've tried it with/without aliases, with/without brackets in different places, I keep getting this error. Can someone help me please?

Without doing research and taking a shot in the dark: I think you forgot to include your "SELECT" in your inner select statement. — Elias
– Elias, Commented Jul 29, 2013 at 17:29
The right sintax of UPDATE doesn't include any FROM: UPDATE aTable AS a INNER JOIN anotherTable AS b SET ... — Barranka
– Barranka, Commented Jul 29, 2013 at 17:38

juergen d · Accepted Answer · 2013-07-29 17:28:41Z

1

UPDATE ShipmentData A
INNER JOIN Customers B ON A.Owner = B.Name
SET A.[Sales Rep] = B.[Sales Rep], 
    A.OfficeNbr = B.OfficeNbr

answered Jul 29, 2013 at 17:28

juergen d

205k40 gold badges303 silver badges377 bronze badges

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

1 Comment

lavaisreallyhot Over a year ago

Thank you very much! Worked like a charm. My syntax are bad.

Collectives™ on Stack Overflow

Can't figure out this syntax error VBA SQL

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related