Wrong number of arguments with SQL ISNULL() on Access DB

Question

I have this query in VB application on Access DB:

  SELECT DISTINCT Specialization, MAX(a.faultZone) AS faultZone, ISNULL(a.faultCount, 0) AS NoOfFaults  FROM Technicians AS t 
    LEFT JOIN 
             ( 
            SELECT DISTINCT Faults.[Type] AS faultType, MAX(Faults.[Zone]) AS faultZone, COUNT(Faults.[Type]) AS faultCount 
            FROM Faults "
            WHERE Faults.[Zone] = 8 " ' this value will be from variable
            GROUP BY Faults.[Type] "
            ) AS a 
    ON (t.Specialization = a.faultType) 
    WHERE t.specialization <> 'None' "
    GROUP BY a.faultCount, t.Specialization 

It gives following problem that I can't solve...

"Wrong number of arguments used with function in query expression 'ISNULL(a.faultCount, 0'."

What I want to achieve is simply set value of NoOFFaults to zero, which would mean there are no faults in particular Zone.

Thank You

Solution 1

Just to add my two cents, and while I like the simple syntax of Nz(), if you seek trouble free performance, both IsNull() and NZ() should be avoided in favor of Is Null:
IIF(a.faultCount Is Null, 0, a.faultCount).

See the excellent explanation here: http://allenbrowne.com/QueryPerfIssue.html

Also, if your tables are in SQL Server or Oracle, using Nz() will force more of the query to be executed locally, with a HUGE performance impact.

Solution 2

I think that you are looking for the nz function

Nz(a.faultCount, 0)

will return 0 if the value is null

Solution 3

Microsoft Access' version of IsNull is different than most SQL versions; it simply returns TRUE if the value is NULL, and FALSE if it isn't.

You need to basically build your own using IIF():

IIF(ISNULL(a.faultCount), 0, a.faultCount)