Solved

IIF IsNull not working

Posted on 2011-03-18
13
280 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

Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

Question has a verified solution.

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

Introduction Hopefully the following mnemonic and, ultimately, the acronym it represents is common place to all those reading: Please Excuse My Dear Aunt Sally (PEMDAS). Briefly, though, PEMDAS is used to signify the order of operations (http://en.…
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…
Email security requires an ever evolving service that stays up to date with counter-evolving threats. The Email Laundry perform Research and Development to ensure their email security service evolves faster than cyber criminals. We apply our Threat…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

830 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