VBA SQL Syntax Error

[Littlemalky]

Hi, I'm having trouble nailing down the syntax in this SQL statement. I'm trying to trigger this code to run upon clicking a button; however, I'm getting a syntax error.

I'm essentially trying to update three fields in a table: Employee Name, Modified By, Modified Date. This was working fine before I added in the Modified By and Modified Date. I have a UDF for UserName().

Names:

Table: [tbl_EMPLOYEE_COST_CENTER_MAP]
Field 1: [EMPLOYEE_FULL_NICK_NAME]
Field 2: [MODIFIED_BY]
Field 3: [MODIFIED_DATE]

Trying to set the [MODIFIED_BY] equal to the environment username and the [MODIFIED_DATE] equal to NOW() so i get a date/time stamp.

strSQL_EENAME = "UPDATE [tbl_EMPLOYEE_COST_CENTER_MAP] SET [EMPLOYEE_FULL_NICK_NAME] = '" & Forms![frm_SALES_EMPLOYEE_MANAGEMENT]!txtEMPLOYEE_NICKNAME_CHANGE & "'" & _                    
",[MODIFIED_BY] = '" & UserName() & "', [MODIFIED_DATE] = '" & Now() & "'," & _
"WHERE [EMPLOYEE_ID] = '" & Forms![frm_SALES_EMPLOYEE_MANAGEMENT]!cmbEMPLOYEE_ID & "'"

 

[james_lankford]

now() needs to be surrounded by #
not single quotes

date fields are always surrounded by #

strings are always surround by single (or double) quotes

 

[Littlemalky]

Hi, thank you for your response. I tried that and I'm still getting a syntax error. I'm not sure if it's related to the Username Part or the Now() part. My public function is as such:

Public Function UserName()    UserName = Environ$("UserName")
End Function

 

[Norie]

You have a comma in front of WHERE.

strSQL_EENAME = " UPDATE [tbl_EMPLOYEE_COST_CENTER_MAP] SET [EMPLOYEE_FULL_NICK_NAME] = '" & Forms![frm_SALES_EMPLOYEE_MANAGEMENT]!txtEMPLOYEE_NICKNAME_CHANGE & "'" & _
",[MODIFIED_BY] = '" & UserName() & "', [MODIFIED_DATE] = #" & Now() & "# " & _
" WHERE [EMPLOYEE_ID] = '" & Forms![frm_SALES_EMPLOYEE_MANAGEMENT]!cmbEMPLOYEE_ID & "'"

Change that to a space and along with James' suggestion you should be OK.

 

[Littlemalky]

That was it! Thank you!

It's the little things that always mess with me...