sql – MS Access 2007中NVL功能的替代方案是什么

select NVL(count(re.rule_status),0) from validation_result re,validation_rules ru where re.cycle_nbr="+cycle_nbr+" and re.rule_response=ru.rule_desc and re.rule_status='FAIL' and ru.rule_category='NAMING_CONVENTION' group by re.rule_status"

但输出是空的.我想将它转换为零.如果我使用NVL功能,则MS Access不接受它.我也尝试了NZ函数,但也提供了相同的输出,即NULL而不是Zero.

解决方法

x = Nz(Null,0)返回0(VbVarType.vbInteger)

x = Nz(Null,“”)返回一个空字符串(VbVarType.vbString)

x = Nz(Null)返回一个空变量(VbVarType.vbEmpty,而不是VbVarType.vbNull)

编辑

进一步调查显示,在您的特定情况下,您在查询中执行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

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!