what is the alternative to NVL function in MS Access 2007

Question

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"

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.

Answer 1

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) returns 0 (VbVarType.vbInteger)

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

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

Edit

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

...which produces the result

SELECT Nz(COUNT(LastName), 0) FROM Members WHERE False GROUP BY LastName
No rows were returned.

When the GROUP BY is removed we get...

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

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

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