Solved

IIF IsNull not working

Posted on 2011-03-18
13
279 Views
Last Modified: 2013-11-05
I have a simple query that I want to return 0 if the result is null. I can't figure out why this isn't working. I have tried it with an Nz function and it still didn't work. Any ideas?

If you need more info on my tables etc, just ask.

 
INSERT INTO qryReportEntries ( [Total Printed], [Total Posted], [Part Number], range )
SELECT IIf(IsNull([CountOfSERL_TS]),0,([CountOfSERL_TS])) AS Expr2, IIf(IsNull([CountOfSERL_NBR_TYPE]),0,([CountOfSERL_NBR_TYPE])) AS Expr1, tblCountAllParts.LIN_CUST_ITEM, TABLERange.range
FROM (tblCountNoActivated RIGHT JOIN tblCountAllParts ON tblCountNoActivated.LIN_CUST_ITEM = tblCountAllParts.LIN_CUST_ITEM) INNER JOIN TABLERange ON tblCountAllParts.LIN_CUST_ITEM = TABLERange.partnumber;

Open in new window

0
Comment
Question by:G Scott
  • 4
  • 4
  • 3
  • +1
13 Comments
 
LVL 57

Accepted Solution

by:
Jim Dettman (Microsoft MVP/ EE MVE) earned 125 total points
ID: 35166223
There are extra ().  Try:

INSERT INTO qryReportEntries ( [Total Printed], [Total Posted], [Part Number], range )
SELECT IIf(IsNull([CountOfSERL_TS]),0,[CountOfSERL_TS]) AS Expr2, IIf(IsNull([CountOfSERL_NBR_TYPE]),0,[CountOfSERL_NBR_TYPE]) AS Expr1, tblCountAllParts.LIN_CUST_ITEM, TABLERange.range
FROM (tblCountNoActivated RIGHT JOIN tblCountAllParts ON tblCountNoActivated.LIN_CUST_ITEM = tblCountAllParts.LIN_CUST_ITEM) INNER JOIN TABLERange ON tblCountAllParts.LIN_CUST_ITEM = TABLERange.partnumber;

 But I would get rid of the IIF's and just do:

INSERT INTO qryReportEntries ( [Total Printed], [Total Posted], [Part Number], range )
SELECT NZ([CountOfSERL_TS],0) AS Expr2, NZ([CountOfSERL_NBR_TYPE],0) AS Expr1, tblCountAllParts.LIN_CUST_ITEM, TABLERange.range
FROM (tblCountNoActivated RIGHT JOIN tblCountAllParts ON tblCountNoActivated.LIN_CUST_ITEM = tblCountAllParts.LIN_CUST_ITEM) INNER JOIN TABLERange ON tblCountAllParts.LIN_CUST_ITEM = TABLERange.partnumber;

JimD.

0
 
LVL 29

Assisted Solution

by:Paul Jackson
Paul Jackson earned 250 total points
ID: 35166228
You don't need the IIF, ISNULL takes two parameters the column to check and the value to return if it is null. Change to :

INSERT INTO qryReportEntries ( [Total Printed], [Total Posted], [Part Number], range )
SELECT IsNull([CountOfSERL_TS],0) AS Expr2, IsNull([CountOfSERL_NBR_TYPE],0) AS Expr1, tblCountAllParts.LIN_CUST_ITEM, TABLERange.range
FROM (tblCountNoActivated RIGHT JOIN tblCountAllParts ON tblCountNoActivated.LIN_CUST_ITEM = tblCountAllParts.LIN_CUST_ITEM) INNER JOIN TABLERange ON tblCountAllParts.LIN_CUST_ITEM = TABLERange.partnumber;
0
 
LVL 2

Assisted Solution

by:tonykachappilly
tonykachappilly earned 125 total points
ID: 35166331
are u using it in MS Access
as far i know
IIF function has 3 parameters
IIF(para1,para2,para3)
para1 - condition returns a bool value
para2 -this value is returned if para1 is true
para3 -this value is returned if para1 is false
u can achieve the same using

SELECT IsNull([CountOfSERL_TS]),0) AS Expr2, IsNull([CountOfSERL_NBR_TYPE]),0) AS Expr1, tblCountAllParts.LIN_CUST_ITEM, TABLERange.range
FROM (tblCountNoActivated RIGHT JOIN tblCountAllParts ON tblCountNoActivated.LIN_CUST_ITEM = tblCountAllParts.LIN_CUST_ITEM) INNER JOIN TABLERange ON tblCountAllParts.LIN_CUST_ITEM = TABLERange.partnumber;

i hope this helps
0
Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

 
LVL 1

Author Comment

by:G Scott
ID: 35166586
Hey, thanks for the replies, unfortunately, none have worked.

JimD, both of yours produced this:

 JimD Error
And jacko, yours gave me this:

 jackoError
tony, this is in Access, and yours tells me there is an extra ).
0
 
LVL 57
ID: 35166618

Do you have a field called CountOfSERL_TS?  Sounds like not.

JimD.
0
 
LVL 1

Author Comment

by:G Scott
ID: 35166696
Do you mean in the destination table, which I added and it still did that, or in here:

Design of query
0
 
LVL 29

Assisted Solution

by:Paul Jackson
Paul Jackson earned 250 total points
ID: 35166700
HmmAccess IsNull is different and works how you were originally using it. try:

INSERT INTO qryReportEntries ( [Total Printed], [Total Posted], [Part Number], range )  
SELECT IIf(IsNull([CountOfSERL_TS]),0,[CountOfSERL_TS]) AS Expr2, IIf(IsNull([CountOfSERL_NBR_TYPE]),0,[CountOfSERL_NBR_TYPE]) AS Expr1, tblCountAllParts.LIN_CUST_ITEM, TABLERange.range  
FROM (tblCountNoActivated RIGHT JOIN tblCountAllParts ON tblCountNoActivated.LIN_CUST_ITEM = tblCountAllParts.LIN_CUST_ITEM) INNER JOIN TABLERange ON tblCountAllParts.LIN_CUST_ITEM = TABLERange.partnumber;
0
 
LVL 2

Expert Comment

by:tonykachappilly
ID: 35166710
OOps thats was an extra '(' used inside the Is null function
try this
SELECT IsNull([CountOfSERL_TS],0) AS Expr2, IsNull([CountOfSERL_NBR_TYPE],0) AS Expr1, tblCountAllParts.LIN_CUST_ITEM, TABLERange.range
FROM (tblCountNoActivated RIGHT JOIN tblCountAllParts ON tblCountNoActivated.LIN_CUST_ITEM = tblCountAllParts.LIN_CUST_ITEM) INNER JOIN TABLERange ON tblCountAllParts.LIN_CUST_ITEM = TABLERange.partnumber;
0
 
LVL 29

Expert Comment

by:Paul Jackson
ID: 35166730
@Tonykachapilly

IsNull in Access only accepts one parameter, it doesn't give the option of the value to replace the null value with
0
 
LVL 1

Author Comment

by:G Scott
ID: 35166739
Ok, I found the error, but it still isn't placing '0's in the table
0
 
LVL 2

Expert Comment

by:tonykachappilly
ID: 35166772
@jacko72 didnt see tony, this is in Access, and yours tells me there is an extra ).

:)
0
 
LVL 29

Expert Comment

by:Paul Jackson
ID: 35166840
So there is :
INSERT INTO qryReportEntries ( [Total Printed], [Total Posted], [Part Number], range )  
SELECT IIf(IsNull([CountOfSERL_TS]),0,[CountOfSERL_TS]) AS Expr2, IIf(IsNull([CountOfSERL_NBR_TYPE],0,[CountOfSERL_NBR_TYPE]) AS Expr1, tblCountAllParts.LIN_CUST_ITEM, TABLERange.range  
FROM (tblCountNoActivated RIGHT JOIN tblCountAllParts ON tblCountNoActivated.LIN_CUST_ITEM = tblCountAllParts.LIN_CUST_ITEM) INNER JOIN TABLERange ON tblCountAllParts.LIN_CUST_ITEM = TABLERange.partnumber;
0
 
LVL 1

Author Closing Comment

by:G Scott
ID: 35171413
Sorry guys, these worked once I fixed my relationships. I needed to move one to the left and do a one to many. Thanks for the help.
0

Featured Post

DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

'Between' is such a common word we rarely think about it but in SQL it has a very specific definition we should be aware of. While most database vendors will have their own unique phrases to describe it (see references at end) the concept in common …
Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
In a recent question (https://www.experts-exchange.com/questions/28997919/Pagination-in-Adobe-Acrobat.html) here at Experts Exchange, a member asked how to add page numbers to a PDF file using Adobe Acrobat XI Pro. This short video Micro Tutorial sh…
This video shows how to quickly and easily add an email signature for all users on Exchange 2016. The resulting signature is applied on a server level by Exchange Online. The email signature template has been downloaded from: www.mail-signatures…

776 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question