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

Patrick Honorez · Accepted Answer · 2016-09-26 09:33:38Z

7

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.

edited Sep 26, 2016 at 9:33

answered Nov 29, 2012 at 20:43

Patrick Honorez

25.4k12 gold badges97 silver badges163 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

HelloW Over a year ago

+1 I like the link. Personally I have never had trouble with Nz but it may be a problem in certain cases

JanT Over a year ago

Extremely I wish I could accept all answers here. I checked link and may rewrite my code. Thanks a lot!

StatsViaCsh Over a year ago

This just got me through much frustration. Very helpful. Thanks.

LittleBobbyTables - Au Revoir · Accepted Answer · 2012-11-29 20:20:49Z

5

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)

answered Nov 29, 2012 at 20:20

LittleBobbyTables - Au Revoir

33k25 gold badges113 silver badges115 bronze badges

Comments

HelloW · Accepted Answer · 2012-11-29 20:27:04Z

5

I think that you are looking for the nz function

Nz(a.faultCount, 0)

will return 0 if the value is null

answered Nov 29, 2012 at 20:27

HelloW

1,6172 gold badges14 silver badges24 bronze badges

2 Comments

JanT Over a year ago

Yes, I just searched for this and it is also good answer. Thx

LittleBobbyTables - Au Revoir Over a year ago

+! - Wow, somehow I've either never seen that, or completely forgotten about it. I love how SQL Server's and Access' versions of ISNULL are completely different.

Collectives™ on Stack Overflow

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

3 Answers 3

3 Comments

Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related