我在VBA中有以下代码:
Dim strSQL As String
strSQL = "UPDATE Workstations SET MID = newvalue WHERE MID = tempvalue"
DoCmd.RunSQL strSQL
newvalue和tempvalue都是全局变量,并且已经被设置了值。语法方面,这样写是否正确?或者我漏掉了引号?
试试这个:
如果MID
是数字:
Dim strSQL As String
strSQL = "UPDATE Workstations SET [MID] = " & newvalue & " WHERE [MID] = " & tempvalue
DoCmd.RunSQL strSQL
MID
是字符串(如果newvalue
/tempvalue
中不包含单引号'
):Dim strSQL As String
strSQL = "UPDATE Workstations SET [MID] = '" & newvalue & "' WHERE [MID] = '" & tempvalue & "'"
DoCmd.RunSQL strSQL
MID
是字符串(如果newvalue
/tempvalue
包含单引号'
,例如newvalue="Mike's car"
):Dim strSQL As String
strSQL = "UPDATE Workstations SET [MID] = '" & Replace(newvalue, "'", "''") & "' WHERE [MID] = '" & Replace(tempvalue, "'", "''") & "'"
DoCmd.RunSQL strSQL
MID
是 日期:Dim strSQL As String
strSQL = "UPDATE Workstations SET [MID] = #" & newvalue & "# WHERE [MID] = #" & tempvalue & "#"
DoCmd.RunSQL strSQL
newvalue
或tempvalue
是包含单引号的字符串会发生什么?你应该对它们进行转义,或者更好的方法是改用参数化语句。 - Chris NielsenUPDATE Workstations w SET w.MID
,这样可以轻松地扩展到其他服务器,并且您永远不必再担心保留字的问题。 - Fionnuala