The Problem (Q-score 6, ranked #40th of 67 in the Access VBA archive)
The scenario as originally posted in 2013
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.
Why this Access DoCmd / Recordset path keeps breaking
The scenario uses DoCmd or OpenRecordset, both of which are notorious for bubbling silent failures when the source query has uncommitted changes. The question captures a common debugging dead-end in Access VBA.
The Verified Solution — niche answer (below median) (+7)
14-line Access VBA pattern (copy-ready)
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
When to Use It — classic (2013–2016)
Ranked #40th in its category — specialized fit
This pattern sits in the 77% tail relative to the top answer. Reach for it when your scenario closely matches the question title; otherwise browse the Access VBA archive for a higher-consensus alternative.
What changed between 2013 and 2026
The answer is 13 years old. The Access VBA object model has been stable across Office 2013, 2016, 2019, 2021, 365, and 2024/2026 LTSC, so the pattern still compiles. Changes that might affect you: 64-bit API declarations (use PtrSafe), blocked macros in downloaded files (Mark-of-the-Web), and the shift toward Office Scripts for web-first workflows.