VBA SQL UPDATE issue

[DJ]

I have an UPDATE SQL statement in VBA which is giving me an error, running
in code, but runs when I copy and paste the SQL into the query window. I am
getting a -2147217900 error, Syntax error in UPDATE statement, from the JET
engine. When I copy the SQL string and place it into a query, I get no
error and the update is made.

Below is the SQL string. All of the field names are correct.

"UPDATE tblGroup SET Active=True, SessionType='Full Day', Status=1,
StatusAsOfDate=#11/14/2005#, StatusComments='', STARLEVEL=3,
STARLEVELAsOfDate=#11/14/2005#, STARLEVELSource='Contracts', NewSpaces=NULL,
Language='', UpdUserID=1, UpdDate=#11/15/2005#, UpdTime=#4:49:50 PM# WHERE
AgencyID='0104' AND CenterID='012' AND ClassroomID='12' AND SessionID='0'
AND GroupID='0'"

Can anyone help me with this? Thank you in advance.

 

[Albert D.Kallal]

Try the following

strSql = "your sql"

debug.print strSql <--- add this line RIGHT before you execute

docmd.RunSql strSql

After your code fails, do a ctrl-g to get to the debug window, now, cut and
paste the sql into the query builder...

does it run un-modifed?

 

[DJ]

I have figured it out. Apparently the Language field was causing a problem
only in VBA. I have changed the field name and it is working. Thanks to
anyone who put any time into this.

 

[tina]

if this is your SQL statement *exactly* as it's saved in the VBA module,
then you need to concatenate separate lines, rather than just enclosing the
whole in double quotes, as

"UPDATE tblGroup SET Active=True, SessionType='Full Day', " _
& "Status=1, StatusAsOfDate=#11/14/2005#, " _
& "StatusComments='', STARLEVEL=3, " _
& "STARLEVELAsOfDate=#11/14/2005#, " _
& "STARLEVELSource='Contracts', NewSpaces=NULL, " _
& "Language='', UpdUserID=1, UpdDate=#11/15/2005#, " _
& "UpdTime=#4:49:50 PM# WHERE AgencyID='0104' " _
& "AND CenterID='012' AND ClassroomID='12' AND " _
& "SessionID='0' AND GroupID='0'"

if you already have the SQL statement correct concatenated, then copy the
SQL statement directly from the VBA code module so we can look at it.

hth