johnyTee johnyTee - 27 days ago 10x
SQL Question

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

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
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
to zero, which would mean there are no faults in particular Zone.

Thank You


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)