Solved

IIF IsNull not working

Posted on 2011-03-18
13
277 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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
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

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
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…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
This tutorial demonstrates a quick way of adding group price to multiple Magento products.

707 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

16 Experts available now in Live!

Get 1:1 Help Now