我在MS Access中编写了一个SQL查询
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.
解决方法
Nz()绝对是你正在寻找的功能.你说你尝试了它并且它返回了Null,但我发现很难相信,因为Nz()的重点是不返回Null.以供参考：
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
