Problems with NULL in queries

[Guinfan]

I've created a querie that sometimes returns a null if there is no relevant data. I think it's a null as there is no info showed, just the "headers" of the columns. Anyway, I need it to show a 0 (zero). Anyone know how I can do this? Thanks

 

[r937]

select iif(isnull(thecolumn),0,thecolumn) 
           as thealias

note you must give it a different name, otherwise access complains about a "circular reference"



rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (next course starts July 10 2005)

 

[Eupher]

Use the Nz function: Expr1: Nz([MyField], 0)

Ken S.

 

[TonyJollans]

Show us the SQL!!

I suspect the issue is in the WHERE clause, not the SELECT itself.

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.
Excel VBA Training and more Help at VBAExpress[

 

[OhioSteve]

New Field Name: nz([Orighinal Field Name],0)

OR

New Field Name: iif([Original Field Name] is null,0,[Original Field Name])

nz() is made to do exactly what you want to do today.

iif() is an incredibly useful function that you should learn. The first part is an expression that's either true or false. If its true, the function returns the first value. If its false, the function returns the second value.

 

[Guinfan]

There is no SQL statement, that I know of. I'm working in access 2002 and setup a query using the design view. As for the rest of your posts, thank you very much, but I have one question, where do I put that code? As you can tell, I'm not very good with this and am learning as I go. Thanks again.

 

[TonyJollans]

Hi Guinfan,

There is SQL - although you may not be working with it. The WHERE clause is what is generated as a result of the "Criteria" you enter in Query design view. What are they for the field in question?

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.
Excel VBA Training and more Help at VBAExpress[

 

[lespaul]

setup a query using the design view

if you switch to the SQL view you will see the SQL statement that was created by the design view. You have to have a SQL statement if you have a query, you just may not be aware of it. If you post that SQL, you will have a greater chance of useful assistance.

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

 

[Guinfan]

Here is the code for the query
SELECT tblCompanyInfoTest.Company, Count(tblRecruitInfoTest.RecruitID) AS CountOfRecruitID
FROM tblGender INNER JOIN (tblCompanyInfoTest INNER JOIN tblRecruitInfoTest ON tblCompanyInfoTest.Company = tblRecruitInfoTest.StartingCompany) ON tblGender.Gender = tblRecruitInfoTest.Gender
GROUP BY tblCompanyInfoTest.Company, tblRecruitInfoTest.Gender
HAVING (((tblRecruitInfoTest.Gender)="Female"));

 

[PHV]

Something like this ?
SELECT tblCompanyInfoTest.Company,
(SELECT Count(*) FROM tblRecruitInfoTest WHERE tblRecruitInfoTest.StartingCompany = tblCompanyInfoTest.Company AND tblRecruitInfoTest.Gender = 'Female') AS CountOfFemaleRecruitID
FROM tblCompanyInfoTest;

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[OhioSteve]

Guinfan, I think that we may have misunderstood your problem. We had thought that you wanted nulls treated as zeros. However, you actually have a grouping query, and you want to change the rules for what constitutes a group. PHV's new sql may fix your problem. Unfortunately it may be difficult for us to explain WHY his syntax is necessary.

 

[Guinfan]

OhioSteve, I do want Nulls treated as 0s. Basically, if my query returns nothing (no females in a company) then I want the query to return a 0. That way I can use that query in other queries and use it for reports.

 

[PHV]

if my query returns nothing (no females in a company) then I want the query to return a 0
Have you tried my suggestion ?

 

[OhioSteve]

Trust me, PHV's solution does have merit. Hmmm, how can I explain this? Okay, lets say that you are doing a grouping query to count the number of baseball teams per city. Your output might look like this~

Kansas City 1
St Louis 1
New York 2
....

Cities with zero teams would NOT appear on the list, and no city would have a zero beside it. That is analogous to your situation. You want to change that arrangement. You want output that looks like this~

Kansas City 1
St Louis 1
New York 2
Honolulu 0
...

PHV's sql may look strange, but he is trying to provide that sort of output. When you initially posted your thread, you used the word "null". But you used the word incorrectly. That's why our initial responses were off the mark. We were trying to fix a problem with nulls. But your problem involves grouping rules, not nulls.

 

[r937]

yes, it involves grouping, and yes, this grouping involves nulls if you use a LEFT OUTER JOIN

select tblCompanyInfoTest.Company
     , sum(iif(isnull(tblRecruitInfoTest.Gender)
                   ,0,tblRecruitInfoTest.Gender) 
           as CountOfFemaleRecruits
  from tblCompanyInfoTest 
let outer 
  join tblRecruitInfoTest 
    on (
       tblCompanyInfoTest.Company 
     = tblRecruitInfoTest.StartingCompany
   and tblRecruitInfoTest.Gender = 'Female'
       )
group 
    by tblCompanyInfoTest.Company

rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (next course starts July 10 2005)

 

[PHV]

rudy, JetSQL chokes on literal in join condition ...

 

[r937]

PHV, you sure? i'm positive i've done that in Access before

that's why i coded the parentheses, i've gotten an error without them, but i'm sure it works with them...

oh well, i could be wrong -- wouldn't be the first time!!



rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (next course starts July 10 2005)