RunSQL Update with Set

bph · August 27th, 2010, 01:05 PM

Howdy,

Getting an error 3144. incorrect syntax, but can't figure what is wrong.

I have 2 tables with identical columns. Table Names = tblEID and tblWT_Temp

I want to update the TermDate column in tblEID with the TermDate value in tblWT_Temp where EMPNUM in both match.

DoCmd.RunSQL Update tblEID
Set tblEID.TermDate = tblWT_Temp.TermDate
WHERE tblWT_Temp.EmpNum = tblEID.EmpNum

I don't want to have to enter a value via the parameter window when the statement runs either.

Any thoughts, greatly appreciated. You all have a good day.

bph

Old Pedant · August 27th, 2010, 03:10 PM

Go to Access HELP, show TABLE OF CONTENTS, find JET SQL REFERENCE.

In that reference, find the UPDATE statement. I think it shows an example of multi-table update.

This machine I'm on doesn't have Access installed, but I know I've done it before on my home machine.

I think it *MIGHT* be as simple as

Code:

Update tblEID, tblWT_Temp
Set tblEID.TermDate = tblWT_Temp.TermDate 
WHERE tblWT_Temp.EmpNum = tblEID.EmpNum

But check the Access documentation to be sure.

bph · August 27th, 2010, 03:55 PM

Thanks OP. I'll check that out. It does seem to be real simple compared to theother stuff I have written.

dabrowne · September 1st, 2010, 04:11 PM

It seems like I can't even get your results of an error message with that syntax. It just prompts me for the values for each if the table's EmpNum fields. I'm using Access 2010, but I wrote it this way:

DoCmd.RunSQL ("UPDATE tblWT_Temp INNER JOIN tblEID ON tblWT_Temp.EmpNum = tblEID.EmpNum SET tblEID.TermDate = tblWT_Temp.TermDate;")

That seems to work for me, if I'm understanding what you want to do, and duplicating enough of your environment. Perhaps the Inner Join between the EmpNum records in the two tables helps Access figure it all out... HTH.

Blessings,

Duff

bph · September 1st, 2010, 04:18 PM

Hi Duff,

You are correct. That piece is working now, don't know why it failed me before.

Is there a way to not have the prompt for the values appears? If empnum matches, go update EID table and proceed with next row in tblWT_Temp, compare empnums and update accordingly. Cursor?

Thanks, bph

dabrowne · September 1st, 2010, 04:30 PM

Hi!

Mine didn't prompt once I did the INNER JOIN. I think it needs that. Did you re-do the SQL to have that relation, and it still prompts you?

Duff

bph · September 1st, 2010, 05:13 PM

Hi Duff,

Awesome!!! Thanks so much for your help.

I put that in, and it ran perfect. Sorry I didn't follow that thought in your first post. I don't understand why it needed the INNER Join temrilogy, as I felt I was acheiving the same thing with the code I wrote.

Good stuff!

bph