CODEWITHSUNDEEP
sqlvb.netms-access
JanT
JanT

Reputation: 2096

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 
    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

Upvotes: 5

Views: 17902

Answers (3)

iDevlop
iDevlop

Reputation: 25252

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.

Upvotes: 7

HelloW
HelloW

Reputation: 1617

I think that you are looking for the nz function

Nz(a.faultCount, 0)

will return 0 if the value is null

Upvotes: 5

LittleBobbyTables - Au Revoir
LittleBobbyTables - Au Revoir

Reputation: 32680

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)

Upvotes: 5

Related Questions