Nz()
绝对是您正在寻找的功能。你说你试过了,结果又回来了Null
,但我发现这很难相信,因为整个要点Nz()
is to not return Null
。以供参考:
x = Nz(Null, 0)
返回 0 (VbVarType.vbInteger
)
x = Nz(Null, "")
返回一个空字符串(VbVarType.vbString
)
x = Nz(Null)
返回一个空变量(VbVarType.vbEmpty
, not VbVarType.vbNull
)
Edit
进一步调查表明,您的具体情况的问题是您正在做COUNT(re.rule_status)
在一个查询中也这样做GROUP BY re.rule_status
。如果WHERE
查询的子句结果为空集(不返回任何行),那么整个查询将不返回任何行,而不是返回值为 0 或 Null 的单行。
这可以通过以下测试代码进行验证...
Sub NzTest()
Dim rst As DAO.Recordset, strSQL As String
strSQL = "SELECT Nz(COUNT(LastName), 0) FROM Members WHERE False GROUP BY LastName"
Debug.Print strSQL
Set rst = CurrentDb.OpenRecordset(strSQL, dbOpenSnapshot)
If rst.EOF Then
Debug.Print "No rows were returned."
Else
Debug.Print "Count = " & rst(0).Value
End If
rst.Close
Set rst = Nothing
End Sub
...产生结果
SELECT Nz(COUNT(LastName), 0) FROM Members WHERE False GROUP BY LastName
No rows were returned.
当。。。的时候GROUP BY
被删除我们得到...
SELECT Nz(COUNT(LastName), 0) FROM Members WHERE False
Count = 0
...事实上Nz()
在这种情况下甚至不需要:
SELECT COUNT(LastName) FROM Members WHERE False
Count = 0