VBA SQL Update

[Jamz]

Hi Guys,

Trying to update a table [WR] with todays date where the text box on the form = ID

    closesql = "UPDATE [WR] SET DateClose '" & Date & "' = WHERE ID = '" & Me.ID & "';"
    DoCmd.RunSQL closesql

Pops up an error message saying there is SYNTAX error in update statement, but i must be being blind

 

[Alansidman]

Look at these two items.

http://allenbrowne.com/ser-71.html

http://www.fontstuff.com/access/acctut15.htm

 

[nanscombe]

closesql = "UPDATE [WR] SET DateClose '" & Date & "' = WHERE ID = '" & Me.ID & "';"
    DoCmd.RunSQL closesql

Your "=" before WHERE is in the wrong place and whereas strings are enclosed in quotes (" or '), Dates are enclosed between hash symbols (#).

I think this will do you.

closesql = "UPDATE [WR] SET DateClose = #" & Date & "# WHERE ID = '" & Me.ID & "'"
    DoCmd.RunSQL closesql

This assumes that Me.ID is a string. If it's a number you'll need.

closesql = "UPDATE [WR] SET DateClose = #" & Date & "# WHERE ID = " & Me.ID
    DoCmd.RunSQL closesql

 

[vbaInet]

Or even:

    closesql = "UPDATE [WR] SET DateClose =[COLOR=Blue] Date() [/COLOR]WHERE ID = '" & Me.ID & "';"
    DoCmd.RunSQL closesql


    closesql = "UPDATE [WR] SET DateClose =[COLOR=Blue] Date()[/COLOR] WHERE ID = " & Me.ID & ";"
    DoCmd.RunSQL closesql