MS Access 2007 中 NVL 功能的替代方法是什么 [英] what is the alternative to NVL function in MS Access 2007

问题描述

我在 MS Access 中编写了一个 SQL 查询

I wrote an SQL query in MS Access

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"

但输出是Null.我想将其转换为 Zero.如果我使用 NVL 功能，那么 MS Access 不接受它.我也尝试了 NZ 函数，但它也给出了相同的输出，即 NULL 而不是零.

But the output is Null. I want to convert it to Zero. If I use NVL function then MS Access does not accept it. I tried NZ function also but that also gives the same output, i.e NULL instead of Zero.

推荐答案

Nz() 绝对是您正在寻找的功能.你说你试过了，它返回了 Null，但我觉得这很难相信，因为 Nz() 的全部意义在于 not 返回 Null.供参考:

Nz() is definitely the function you're looking for. You say that you tried it and it returned Null, but I find that hard to believe because the whole point of Nz() is to not return Null. For reference:

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

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

x = Nz(Null, "") returns an empty string (VbVarType.vbString)

x = Nz(Null) 返回一个空变量(VbVarType.vbEmpty，not VbVarType.vbNull)

x = Nz(Null) returns an empty variable (VbVarType.vbEmpty, not VbVarType.vbNull)

进一步调查表明，在您的特定情况下，问题在于您在执行 GROUP BY re.rule_status 的查询中执行了 COUNT(re.rule_status).如果查询的 WHERE 子句导致空集(不返回行)，则整个查询只返回不返回行，而不是返回值为 0 或 Null 的单个行.

Further investigation shows that the problem in your particular case is that you are doing a COUNT(re.rule_status) in a query that also does GROUP BY re.rule_status. If the WHERE clause of the query results in an empty set (no rows returned) then the overall query simply returns no rows instead of a single row with a value of 0 or Null.

这可以通过下面的测试代码来验证...

This can be verified with the following test code...

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 被删除时，我们得到...

When the GROUP BY is removed we get...

SELECT Nz(COUNT(LastName), 0) FROM Members WHERE False
Count = 0

...事实上，在这种情况下甚至不需要 Nz() :

...and in fact Nz() is not even required in that case:

SELECT COUNT(LastName) FROM Members WHERE False
Count = 0

这篇关于MS Access 2007 中 NVL 功能的替代方法是什么的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！