SQL Update

[IanT]

Hi

I am using the SQL statement below with a variable which changes via a loop, can anyone advise where the SQL is incorrect as I get an error.

DoCmd.RunSQL "UPDATE" & myvar & "SET" & myvar & ".LOB = 'MOTOR'" & _
"WHERE (((" & myvar & ".LOB) Like '*mot*'));"

 

[DCrake]

First of all do a Debug.print to view the actual sql statement

Debug.Print "UPDATE" & myvar & "SET" & myvar & ".LOB = 'MOTOR'" & _
"WHERE (((" & myvar & ".LOB) Like '*mot*'));"

Then see what it looks like.

Also you have not said what error you are getting.

 

[Kenln]

I know this sounds foolish, but a common problem I have when creating SQL like your example is remembering to include spaces.

For example
If myvar = "tbl_My_Table" then your script

"UPDATE" & myvar

might give this result

Updatetbl_My_Table

Instead of

Update tbl_My_Table

So... you could try

"UPDATE " & myvar

Just a problem that I know I have to watch for.