DoCmdRunSQL

[Guest]

I am having to maintain an update process that was created by the person who
previously held my position. I don't know much of anything about the syntax
for sql statements.

I have an sql statement that will find a user and disable them. I need to
also update a field called COMMENTS that is also in the table. I was to
append the phrase "User disabled by Nightly Process" and the current date to
the entry already in the comments field.

Here is what I have:
strSQL = "UPDATE dbo_tblHPCUsers SET IsActive = 1, " & _
"TerritoryID = null, WebAccess = 0, Comments = "User
disabled by Nightly Process" & Date & Comments " & _
"WHERE (((dbo_tblHPCUsers.UserId)=" & idealid & " Or
(dbo_tblHPCUsers.UserId)=" & cstoneid & " " & _
"Or (dbo_tblHPCUsers.UserId)=" & heritageid & "));"

I get a compile error expected: end of statement.

Please tell me what I'm doing wrong.

thanks!

 

[George Nicholson]

Try:
strSQL = "UPDATE dbo_tblHPCUsers SET IsActive = 1, " & _
"TerritoryID = null, WebAccess = 0, " & _
"Comments = 'User disabled by Nightly Process' " & Date & "
" & Comments & _
" WHERE (((dbo_tblHPCUsers.UserId)=" & idealid & _
" Or (dbo_tblHPCUsers.UserId)=" & cstoneid & " " & _
"Or (dbo_tblHPCUsers.UserId)=" & heritageid & "));"

Note especially the single quotes around 'User disabled by Nightly Process',
the space between Date and Comments, and the leading space before WHERE.
In my experience, "Expected end of statement" errors are usually due to a
problem with double-quote placement (or an odd number of double quotes since
they are expected in pairs).

You can put a breakpoint on the line immediately following this one and,
when reached, type ?strSQL in the Immediate window. The result is the string
you have just constructed. Giving it a quick look often makes clear what the
problem is.

HTH,

 

[Guest]

Never mind I got it to work! Sorry!

 

[Guest]

Thank you!

Try:
strSQL = "UPDATE dbo_tblHPCUsers SET IsActive = 1, " & _
"TerritoryID = null, WebAccess = 0, " & _
"Comments = 'User disabled by Nightly Process' " & Date & "
" & Comments & _
" WHERE (((dbo_tblHPCUsers.UserId)=" & idealid & _
" Or (dbo_tblHPCUsers.UserId)=" & cstoneid & " " & _
"Or (dbo_tblHPCUsers.UserId)=" & heritageid & "));"

Note especially the single quotes around 'User disabled by Nightly Process',
the space between Date and Comments, and the leading space before WHERE.
In my experience, "Expected end of statement" errors are usually due to a
problem with double-quote placement (or an odd number of double quotes since
they are expected in pairs).

You can put a breakpoint on the line immediately following this one and,
when reached, type ?strSQL in the Immediate window. The result is the string
you have just constructed. Giving it a quick look often makes clear what the
problem is.

HTH,