Solved

IIF IsNull not working

Posted on 2011-03-18
13
278 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
 
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
DevOps Toolchain Recommendations

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

 
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
oracle query help 18 99
Getting certain data from a string 1 34
Query syntax 10 40
What query can i write to find where a function is 4 15
Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
With the power of JIRA, there's an unlimited number of ways you can customize it, use it and benefit from it. With that in mind, there's bound to be things that I wasn't able to cover in this course. With this summary we'll look at some places to go…

911 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

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now