如果/那么在 VBA 中嵌入的 SQL 中
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3048736/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
If/Then in SQL Embedded in VBA
提问by Daniel
I've got a string like this in my Excel VBA:
我的 Excel VBA 中有一个这样的字符串：
strSQL = "SELECT * FROM Total WHERE (SimulationID = (" & TextBox1.Text & ") And Test1 = (" & Example & "))"
However, sometimes Test will be 'is null', which makes the query
但是，有时 Test 会是 'is null'，这使得查询
And Example = is NULL
How can I change it to add an if/then statement or something to make it say
如何更改它以添加 if/then 语句或其他内容以使其说明
And Example is null
when Example has a value of "is null"?
当示例的值为“为空”时？
回答by ajdams
I would suggest doing the NULL comparison before assembling the SQL statement strSQL. If you check for the value of Example beforehand, you can alter your strSQL statement accordingly based on that check.
我建议在组装 SQL 语句 strSQL 之前进行 NULL 比较。如果您事先检查 Example 的值，您可以根据该检查相应地更改您的 strSQL 语句。
EDIT:
In response to Daniel's first and second comment below, I still would prefer the following over doing it inline:
编辑：
为了回应丹尼尔在下面的第一条和第二条评论，我仍然更喜欢以下内容而不是内联：
Dim strSqlTail strSqlTail = ""
Dim strSqlTail strSqlTail = ""
If (Example1 = Null) Then strSqlTail = "AND Example1 IS NULL"
If (Example2 = Null) Then strSqlTail = strSqlTail & "AND Example2 IS NULL"
If (Example3 = Null) Then strSqlTail = strSqlTail & "AND Example3 IS NULL"
If (Example1 = Null) Then strSqlTail = "AND Example1 IS NULL"
If (Example2 = Null) Then strSqlTail = strSqlTail & "AND Example2 IS NULL"
If (Example3 = Null) Then strSqlTail = strSqlTail & "AND Example3 IS NULL"
...
...
Note: The strSqlTail can be whatever SQL would make your situation work since I don't quite understand what is being queried from the sample.
注意： strSqlTail 可以是任何可以使您的情况正常工作的 SQL，因为我不太明白从示例中查询的内容。
回答by Thomas
One solution is to coalesce out the null using a Coalesce function (or if using Access, the Nz function) :
一种解决方案是使用 Coalesce 函数（或者如果使用 Access，则为 Nz 函数）合并空值：
trSQL = "SELECT ..." & _
" FROM Total" & _
" WHERE SimulationID = " & TextBox1.Text & " & _
" And Coalesce(Test1,"""") = "" & Example & """
A better way would be to dynamically include or not include the entire Andstatement based on whether Examplehad a value or to substitute Test Is Nullwhen Exampledid not have a value. So something akin to:
更好的方法是And根据是否Example有值动态包含或不包含整个语句，或者Test Is Null在Example没有值时替换。所以类似于：
Dim testElements() As Variant
Dim vElement As Variant
Redim testElements(6,1)
testElements(0,0) = Example1
testElements(0,1) = "Test1"
testElements(1,0) = Example2
testElements(1,1) = "Test2"
...
Dim elementIndex as Integer
Dim colName As String
Dim elementValue As Variant
For elementIndex = 0 To UBound(testElements)
elementValue = testElement(elementIndex, 0)
colName = testElement(elementIndex, 1)
If Len(Nz(elementValue)) = 0 Then
trSQL = trSQL & " And " & colName & " = """ & Example1 & """
Else
trSQL = trSQL & " And " & colName & " Is Null"
End If
Next
回答by Lance Roberts
You just create a function that puts the equal sign and space before the number if it doesn't equal "is null", and modify the string assignment statement appropriately, like so:
您只需创建一个函数，如果它不等于“is null”，则将等号和空格放在数字之前，并适当地修改字符串赋值语句，如下所示：
Public Function NullModString(Example As String) As String
If Example <> "is null" Then Example = "= " & Example
NullModString = Example
End Function
strSQL = "SELECT * FROM Total WHERE SimulationID = " & TextBox1.Text & _
"And Test1 " & NullModString(Example)
Note, that I didn't understand why the extra parentheses were in there, but it would be simple to put them back in.
请注意，我不明白为什么在那里有额外的括号，但将它们放回去很简单。
回答by onedaywhen
First, don't embed SQL in VBA: hard to maintain, SQL injection, etc. Use a stored proc on the database side (even Access has PROCEDUREs).
首先，不要在 VBA 中嵌入 SQL：难以维护、SQL 注入等。在数据库端使用存储过程（即使 Access 有PROCEDUREs）。
Second, use COALESCE(or equivalent logic) to handle your 'empty string equates to NULL' UI logic. You don't say which SQL syntax and you haven't posted schema DLL so we're merely guessing...
其次，使用COALESCE（或等效逻辑）来处理您的“空字符串等同于NULL” UI 逻辑。您没有说哪种 SQL 语法，也没有发布架构 DLL，所以我们只是在猜测……
SQL Server:
SQL 服务器：
CREATE PROCEDURE GetTotals
@SimulationID INTEGER,
@Test1 VARCHAR(20) = NULL
AS
SELECT *
FROM Total AS T1.
WHERE T1.SimulationID = @SimulationID
AND COALESCE(T1.Test1, '') = COALESCE(@Test1, '');
Access Database Engine (a.k.a. Jet):
访问数据库引擎（又名 Jet）：
CREATE PROCEDURE GetTotals
(
:SimulationID INTEGER,
:Test1 VARCHAR(20) = NULL
)
AS
SELECT *
FROM Total AS T1
WHERE T1.SimulationID = :SimulationID
AND IIF(T1.Test1 IS NULL, '', T1.Test1)
= IIF(:Test1 IS NULL, '', :Test1);
Then execute the proc using your middleware (ADO, DAO, etc) with Parameter objects, using the empty string (or other 'magic' value) for NULL.
然后使用带有 Parameter 对象的中间件（ADO、DAO 等）执行 proc，使用NULL.
