更新SQL MS Access 2010 [英] Update SQL MS Access 2010

问题描述

这使我的大脑崩溃了四个小时，

This is wrecking my brains for 4 hours now,

我有一个名为BreakSked的表，

I have a Table named BreakSked,

和我这个按钮来更新表与这个SQL的休息结束时间:

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 保存当前系统时间，而 List423 包含人员名称.

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

我总是得到这个

运行时错误3075:查询中的语法错误(缺少运算符) 表达式'03:00:00 am'

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

请帮忙吗?

谢谢，现在我的记录正在更新.但是现在，它添加了另一个记录，而不是更新手头的记录.我感到很傻，因为我的程序只有两个按钮，我不知道为什么会这样.

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

推荐答案

正如我在评论中所说，您需要将日期字段包装在#"中，并将字符串字段包装在转义的双引号中

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'"

这篇关于更新SQL MS Access 2010的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！