johnyTee johnyTee - 1 year ago 219
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

Answer Source

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)
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download