About Update statement with table variable

[moepusu]

Hi all

I have problem using table variable in update statement.
Please see below.

mpyemp="febpyemp" && this file name will change everymonth

in my update statement

DoCmd.RunSQL "update pyemp INNER JOIN {Mpyemp} 
  ON  (pyemp.PASS={Mpyemp}.PASS) 
  AND (pyemp.SSN=@tpyemp.SSN) 
  SET pyemp.budrc = {Mypemp}.budrc 
  WHERE (IsNull(pyemp.BUDRC)) Or (pyemp.BUDRC) = '0000';"

when I run the program. the error shown as below

run-time error 3144

Syntax Error in update statement. So someone please let me know the right statement using Mpyemp variable.
Thank you in advanced
Moe

[Rabbit]

strSQL = "SELECT * FROM " & tableVariable & ";"

[moepusu]

Still problem with following command

DoCmd.RunSQL "update pyemp INNER JOIN " & MPYEMP & " ON (pyemp.PASS="&mpyemp&".PASS) AND (pyemp.SSN="&mpyemp&".SSN) SET pyemp.budrc = "&mypemp&".budrc WHERE (IsNull(pyemp.BUDRC)) Or (pyemp.BUDRC) = '0000';"

[TheSmileyCoder]

I can't say if you directly copy/pasted your code, but the spaces between the & are important, and they seem to be missing from your code.

DoCmd.RunSQL "update pyemp 
  INNER JOIN " & MPYEMP & " 
  ON (pyemp.PASS=" & mpyemp & ".PASS)
  AND (pyemp.SSN=" & mpyemp & ".SSN) 
  SET pyemp.budrc = " & mypemp & ".budrc 
  WHERE (IsNull(pyemp.BUDRC)) Or (pyemp.BUDRC) = '0000';"

If this doesn't work, I would suggest you start by forming your SQL string into a string variable, and then post that variable into a msgbox so that you can "proofread" it.

[moepusu]

TheSmileyCoder

Thank you so much for your help
It works well.