Update Query using Alias and Joins

[Halliarse]

Good Evening

I'm struggling to get past an error 3075 - Missing Operator and would like some advise please?

I have an Access database with the following tables:

tblOrderMth
tblOrderRFP
tblStatusList

tblOrderMTH contains the required Order Number in field OrderNO

The tables are joined as follows:

tblOrderRFP.RFP = tblStatusList.SubOrder
tblOrderMTH.ID = tblOrderRFP.OrderNo

I need to set the AltManu field on the tblStatusList table equal to a selected value from a dropdown list

The code I'm using is as below:

sql = "UPDATE S Set AltManu = """ & Me.cboPlantTo & """ FROM tblStatusList S INNER JOIN tblOrderRFP R ON S.SubOrder = R.RFP INNER JOIN tblOrderMth M ON R.OrderNo = M.ID where M.OrderNo = """ & Me.cboOrderNo & """);"

db.Execute (sql)

I hope that makes sense to someone!

Thanks

Steve

 

[MajP]

after you build your string put a debug.print so we can see the sql string that gets formed.
Not sure of your "". Try this

sql = "UPDATE S Set AltManu = '" & Me.cboPlantTo & "' FROM tblStatusList S INNER JOIN tblOrderRFP R ON S.SubOrder = R.RFP INNER JOIN tblOrderMth M ON R.OrderNo = M.ID where M.OrderNo = '" & Me.cboOrderNo & "'"

 

[Halliarse]

Hi MajP

Thanks for the reply.

I've used your version of syntax and I still get the same 3075 error....attached is the SQL statement:

Thanks again

Steve

 

[MajP]

I am not great at reading SQL and troubleshooting. If it was me I would build that exact string in the query designer using the values 1hk and 6337406 and see what the SQL looks like. Any chance the OrderNo is a real numeric. If so

R.OrderNo = M.ID where M.OrderNo = " & Me.cboOrderNo

 

[Halliarse]

No, it's definitely a text field

 

[dhookom]

I would try this without all of the alias'.

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[Halliarse]

Hi dhookothanks for the response, unfortunately this still returns the same error!

 

[MajP]

Debug.print the sql and then drop it into the QDE and see if it points to the syntax error. May be a spelling error.

 

[Halliarse]

Apologies for my ignorance MajP but what is QDE and where do I find it?

 

[MajP]

Sorry. Sometimes the query designer is referred to as the query development environment (QDE). Should have said query designer.

 

[Halliarse]

MajP

I've done as you suggested and I've also changed the order of the Join's as below! I'm now getting a join issue on the tblOrderRFP....it highlights in black when I run the query through the designer! The table name is correct, including case, and the field name also exists....I'm at a proper loss now!

Update (tblStatusList INNER JOIN tblStatusList ON tblOrderRFP.RFP = tblStatusList.suborder) INNER JOIN tblOrderMth ON tblOrderRFP.OrderNo = tblorderMth.ID Set tblStatusList.ALTManu = '1HK' where tblOrderMth.OrderNo = '6337406'