Link to home
Start Free TrialLog in
Avatar of valmatic
valmaticFlag for United States of America

asked on

Syntax wrong on RunSql Update over Innerjoin

Hi.  I don't get any errors on this but my table is not updating accordingly.  I tried changing the syntax around a bit and keep getting syntax errors.  I know I'm close but can't quite get it to work.   What is the correct syntax, especially when working with text fields?  thanks
DoCmd.RunSQL "UPDATE RGInfo INNER JOIN RGItem ON RGInfo.RGNo = RGItem.RGNo SET(RGItem.TrueDispo = 'Scrapped by Customer') WHERE(((RGInfo.RGEndDate) Is Not Null) AND (((RGInfo.RtnFrt)='Customer Scrap')))"

Open in new window

Avatar of Arthur_Wood
Arthur_Wood
Flag of United States of America image

I don't think you can update the Right-hand table on an Inner Join.  

try the sql like this:

DoCmd.RunSQL "UPDATE RGItem INNER JOIN RGInfo ON RGItem.RGNo = RGInfo.RGNo SET(RGItem.TrueDispo = 'Scrapped by Customer') WHERE(((RGInfo.RGEndDate) Is Not Null) AND (((RGInfo.RtnFrt)='Customer Scrap')))"

AW
Avatar of valmatic

ASKER

Hi Arthur
I get a syntax error when i run your code.   (Syntax error in UPDATE statement)

I removed a set of parenthesis around each of the field names with text values.  I didn't get an error but my table doesn't update.
ASKER CERTIFIED SOLUTION
Avatar of Arthur_Wood
Arthur_Wood
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Avatar of Jeffrey Coachman
Jeffrey Coachman
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Hi guys,  Got pulled away.  A couple more days and I can get back on this - apologies...

Do appreciate the help though :)
HI. Thanks for being so patient with me.  

That did the trick..  I used Jeff's code but am giving you the lion's share of the points Art.  thanks for the help both of you...