2

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 [BreakSked] SET [BreakSked].[EndTime] = " & _ Me.Text412.Value & " WHERE [BreakSked].AgentName = " & Me.List423.Value _ & " AND [BreakSked].ShiftStatus = '1'" CurrentDB.Execute strSQL1

Text412 holds the current system time and List423 contains the name of the person.

I'm always getting this

"Run-time error 3075: Syntax Error (missing operator) in query expression '03:00:00 am'

Any help please?

EDIT: Thanks, now my records are updating. But now its adding another record instead of updating the record at hand. I feel so silly since my program only has two buttons and I can't figure out why this is happening.

Private Sub Form_Load() DoCmd.GoToRecord , , acNewRec End Sub Private Sub Command536_Click() strSQL1 = "UPDATE BreakSked SET BreakSked.EndTime = '" & Me.Text412.Value & "',BreakSked.Duration = '" & durationz & "' " & vbCrLf & _ "WHERE (([BreakSked].[AgentID]='" & Me.List423.Value & "'));" CurrentDb.Execute strSQL1 CurrentDb.Close MsgBox "OK", vbOKOnly, "Added" End Sub Private Sub Command520_Click() strSql = "INSERT INTO BreakSked (ShiftDate,AgentID,StartTime,Status) VALUES ('" & Me.Text373.Value & "', '" & Me.List423.Value & "', '" & Me.Text373.Value & "','" & Me.Page657.Caption & "')" CurrentDb.Execute strSql CurrentDb.Close MsgBox "OK", vbOKOnly, "Added" End Sub
Improve this question
1
  • wrap your datetime field in '#' date operators and wrap your text in double quotes Commented Jul 25, 2018 at 1:06

2 Answers 2

Reset to default
3

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

Dim strUpdate As String Dim 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 & _
Improve this answer
Sign up to request clarification or add additional context in comments.

2 Comments

And you can then save the parameterized query, rather than having to hard-code it in your VBA. Something like set qdf = db.queryDefs("qAddBreakEndTime")
Thanks its now updating. But now Im getting where my insert SQL adds another record instead of updating it
1

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'"
Improve this answer

4 Comments

Thanks its now updating. But now Im getting where my insert SQL adds another record instead of updating it
You're opening your form as a New Record - that means you've just inserted a new record.You need to mark an answer and start a new question not just keep adding coding problems to current question
I see thank you. I'll open another one then. Appreciate the assistance
It's a long and winding road ahead - but I know you'll enjoy the coding

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.