INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Update Query using Alias and Joins

Update Query using Alias and Joins

(OP)
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

RE: Update Query using Alias and Joins

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

CODE -->

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 & "'" 

RE: Update Query using Alias and Joins

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

CODE -->

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

RE: Update Query using Alias and Joins

(OP)
No, it's definitely a text field

RE: Update Query using Alias and Joins

I would try this without all of the alias'.

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

RE: Update Query using Alias and Joins

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

RE: Update Query using Alias and Joins

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.

RE: Update Query using Alias and Joins

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

RE: Update Query using Alias and Joins

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

RE: Update Query using Alias and Joins

(OP)
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'

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close