Update Sql Ms Access 2010

This is wrecking my brains for 4 hours now, I have a Table named BreakSked, and I this button to update the table with the break end time with this sql: strSQL1 = 'UPDATE [Break

Solution 1:

You wouldn't need to delimit Date/Time and text values if you use a parameter query.

Dim strUpdate AsStringDim db As DAO.Database
Dim qdf As DAO.QueryDef

strUpdate = "PARAMETERS pEndTime DateTime, pAgentName Text ( 255 );" & vbCrLf & _
    "UPDATE BreakSked AS b SET b.EndTime = [pEndTime]" & vbCrLf & _
    "WHERE b.AgentName = [pAgentName] AND b.ShiftStatus = '1';"
Debug.Print strUpdate ' <- inspect this in Immediate window ...'    Ctrl+g will take you there Set db = CurrentDb
Set qdf = db.CreateQueryDef("", strUpdate)
qdf.Parameters("pEndTime").Value = Me.Text412.Value
qdf.Parameters("pAgentName").Value = Me.List423.Value
qdf.Execute dbFailOnError

And if you always want to put the current system time into EndTime, you can use the Time() function instead of pulling it from a text box.

'qdf.Parameters("pEndTime").Value = Me.Text412.Value
qdf.Parameters("pEndTime").Value = Time()  ' or Now() if you want date and time

However, if that is the case, you could just hard-code the function name into the SQL and dispense with one parameter.

"UPDATE BreakSked AS b SET b.EndTime = Time()" & vbCrLf & _

Solution 2:

As I said in my comment you need to wrap date fields in "#" and string fields in escaped double quotes

strSQL1 = "UPDATE [BreakSked] SET [BreakSked].[EndTime] = #" & _  
Me.Text412.Value & "# WHERE [BreakSked].AgentName = """ & Me.List423.Value & _
""" AND [BreakSked].ShiftStatus = '1'"