我假设您的代码包含错别字(无括号),并且实际上应该阅读:
WHERE IIf([GrpOrder]=3,IIf([LabelText]="Totals",True,False),True) = true
从sql代码的角度来看,由于sql具有以下三种值逻辑,实际上需要考虑九种情况NULL
:
GrpOrder = 3
GrpOrder <> 3
GrpOrder IS NULL
LabelText = 'Totals'
LabelText <> 'Totals'
LabelText IS NULL
总共有九种情况,例如测试数据和结果:
OP_result | GrpOrder | LabelText
----------------------------------
TRUE | 55 | 'Totals'
TRUE | 55 | 'Tallies'
TRUE | 55 | <NULL>
TRUE | 3 | 'Totals'
FALSE | 3 | 'Tallies'
FALSE | 3 | <NULL>
TRUE | <NULL> | 'Totals'
TRUE | <NULL> | 'Tallies'
TRUE | <NULL> | <NULL>
最安全的方法是写出一系列OR
子句,NULL
对每个OR
子句的两列进行明示处理。但是,这很漫长,最好标记这两个返回FALSE的情况。这就是大多数人(包括我!)遇到NULL问题的地方:这太违反直觉了!
例如,写这个很诱人:
(GrpOrder = 3 AND LabelText IS NULL)
OR
(GrpOrder = 3 AND LabelText <> 'Totals')
然后使用NOT
以下方法“翻转”其值:
NOT (
(GrpOrder = 3 AND LabelText IS NULL)
OR
(GrpOrder = 3 AND LabelText <> 'Totals')
)
但是,这样做会NULL
潜入结果集中:
OP_result | attempt_1 | GrpOrder | LabelText
---------------------------------------------
TRUE | TRUE | 55 | 'Totals'
TRUE | TRUE | 55 | 'Tallies'
TRUE | TRUE | 55 | <NULL>
TRUE | TRUE | 3 | 'Totals'
FALSE | FALSE | 3 | 'Tallies'
FALSE | FALSE | 3 | <NULL>
TRUE | TRUE | <NULL> | 'Totals'
TRUE | <NULL> | <NULL> | 'Tallies'
TRUE | <NULL> | <NULL> | <NULL>
因此,我们需要显式处理更多的情况,而不是乍一看。
我可以想到的最简单的谓词可以在Access中获得所需的结果:
NOT
(
(LabelText <> 'Totals' OR LabelText IS NULL)
AND GrpOrder = 3
AND GrpOrder IS NOT NULL
)
[…阅读起来很奇怪,我想知道OP的代码是否首先产生了期望的结果。]
要学习的主要课程:
@Yanir Kleiman评论:
GrpOrder不能同时为3和NULL,因此在这种情况下检查它是否不为null是多余的
可以这样认为是可以原谅的。但这就是Access :)我们拥有声称符合sql标准的sql产品的出色规范。Access声称没有这种合规性,Access团队提供的文档的质量特别低。
相反,在Access-land中,要使某件事真实,您必须进行实际测试!
当我删除谓词时
AND GrpOrder IS NOT NULL
空将出现在结果集中。尽管感觉像“违背逻辑”,但请记住,sql的三值逻辑仅在Access声明不符合的规范中定义。如果访问团队没有告诉我们该产品应该如何工作,那么我们如何确定以上内容是错误还是功能?即使我们可以说服他们这是一个错误,他们也会解决吗?
下面,我提供了VBA代码来重现此问题:只需将+复制粘贴到任何VBA模块中,无需设置引用。它在temp文件夹中创建一个新的.mdb,然后创建表和测试数据。Access不需要安装在计算机上,例如,使用Excel的VBA编辑器。
该消息框显示了分别包含和删除上述谓词时的结果集。除了两个表列之外,还有两个计算列显示为值-1(TRUE),0(FALSE)和NULL,最左边的是OP:
Sub AccessStrangeLogic()
On Error Resume Next
Kill Environ$("temp") & "\DropMe.mdb"
On Error GoTo 0
Dim cat
Set cat = CreateObject("ADOX.Catalog")
With cat
.Create _
"Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & _
Environ$("temp") & "\DropMe.mdb"
With .ActiveConnection
Dim sql As String
sql = _
"CREATE TABLE GrpOrders" & vbCr & _
"(" & vbCr & _
" GrpOrder INTEGER," & vbCr & _
" LabelText NVARCHAR(10)" & vbCr & _
");"
.Execute sql
sql = _
"INSERT INTO GrpOrders (GrpOrder, LabelText)" & _
" VALUES (55, 'Totals');"
.Execute sql
sql = _
"INSERT INTO GrpOrders (GrpOrder, LabelText)" & _
" VALUES (55, 'Tallies');"
.Execute sql
sql = _
"INSERT INTO GrpOrders (GrpOrder, LabelText)" & _
" VALUES (55, NULL);"
.Execute sql
sql = _
"INSERT INTO GrpOrders (GrpOrder, LabelText)" & _
" VALUES (3, 'Totals');"
.Execute sql
sql = _
"INSERT INTO GrpOrders (GrpOrder, LabelText)" & _
" VALUES (3, 'Tallies');"
.Execute sql
sql = _
"INSERT INTO GrpOrders (GrpOrder, LabelText)" & _
" VALUES (3, NULL);"
.Execute sql
sql = _
"INSERT INTO GrpOrders (GrpOrder, LabelText)" & _
" VALUES (NULL, 'Totals');"
.Execute sql
sql = _
"INSERT INTO GrpOrders (GrpOrder, LabelText)" & _
" VALUES (NULL, 'Tallies');"
.Execute sql
sql = _
"INSERT INTO GrpOrders (GrpOrder, LabelText)" & _
" VALUES (NULL, NULL);"
.Execute sql
' Include "AND GrpOrder IS NOT NULL"
sql = _
"SELECT *, " & vbCr & _
" IIf([GrpOrder]=3,IIf([LabelText]=""Totals"",True,False),True) = true AS OP_result, " & vbCr & _
" NOT" & vbCr & _
" (" & vbCr & _
" (LabelText <> 'Totals' OR LabelText IS NULL)" & vbCr & _
" AND GrpOrder = 3 " & vbCr & _
" AND GrpOrder IS NOT NULL" & vbCr & " )" & vbCr & _
" FROM GrpOrders" & vbCr & _
" ORDER " & vbCr & _
" BY GrpOrder DESC, LabelText DESC;"
Dim rs
Set rs = .Execute(sql)
' Remove "AND GrpOrder IS NOT NULL"
sql = Replace$(sql, "AND GrpOrder IS NOT NULL", "")
Dim rs2
Set rs2 = .Execute(sql)
Msg@R_542_2419@ _
"Include 'AND GrpOrder IS NOT NULL':" & vbCr & _
rs.GetString(, , vbTab, vbCr, "<NULL>") & vbCr & _
"remove 'AND GrpOrder IS NOT NULL':" & vbCr & _
rs2.GetString(, , vbTab, vbCr, "<NULL>")
End With
Set .ActiveConnection = Nothing
End With
End Sub