Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

IIF IsNull not working

Posted on 2011-03-18
13
Medium Priority
?
285 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 58

Accepted Solution

by:
Jim Dettman (Microsoft MVP/ EE MVE) earned 500 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 1000 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 500 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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

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

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

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

As they say in love and is true in SQL: you can sum some Data some of the time, but you can't always aggregate all Data all the time! Introduction: By the end of this Article it is my intention to bring the meaning and value of the above quote to…
This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
this video summaries big data hadoop online training demo (http://onlineitguru.com/big-data-hadoop-online-training-placement.html) , and covers basics in big data hadoop .
Screencast - Getting to Know the Pipeline

916 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