在SQL语句中的全局变量 [英] Global Variables in SQL statement

问题描述

我有以下的code在VBA：

I have the following code in VBA:

Dim strSQL As String
strSQL = "UPDATE Workstations SET MID = newvalue WHERE MID = tempvalue"
DoCmd.RunSQL strSQL

newvalue中和tempvalue都是全局变量和已经设置的值。语法明智的，这是否有道理？还是我失去了引号？

newvalue and tempvalue are both global variables and have already been set values. Syntax wise, does this make sense? or am I missing quotation marks?

推荐答案

试试这个：

如果 MID 是号的：

Dim strSQL As String
strSQL = "UPDATE Workstations SET [MID] = " & newvalue & " WHERE [MID] = " & tempvalue
DoCmd.RunSQL strSQL

如果 MID 是字符串的（如 newvalue中 / tempvalue 不包含单引号）：

If MID is string (if newvalue/tempvalue doesn't contain single quote '):

Dim strSQL As String
strSQL = "UPDATE Workstations SET [MID] = '" & newvalue & "' WHERE [MID] = '" & tempvalue & "'"
DoCmd.RunSQL strSQL

如果 MID 是字符串的（如 newvalue中 / tempvalue 包含单引号如为newValue =小李的车）：

If MID is string (if newvalue/tempvalue contains single quote ' like 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的指出了意见，认为

Thanks to @HansUp for pointing out in comments, that

MID是一个函数的名称，所以这将是更安全的括号或别名在SQL语句中的字段名称：SET [MID]

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]

这篇关于在SQL语句中的全局变量的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！