SQL语句中的全局变量

我在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

感谢@HansUp在评论中指出,那

MID is the name of a function,so it would be safer to bracket or alias that field name in the SQL statement: SET [MID]

（编辑：ASP站长）

【免责声明】本站内容转载自互联网，其相关言论仅代表作者个人观点绝非权威，不代表本站立场。如您发现内容存在版权问题，请提交相关链接至邮箱：bqsm@foxmail.com，我们将及时予以处理。