• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1357
  • Last Modified:

Show Null Count value as Zero "0" instead of blank

Hello,

    I need to display a 0 (zero) in a Listbox instead of a blank.  The RowSource is listed below.  I've looked though some example such as NZ and Transform but this is NOT a crosstab query.  Thank you in advance for any guidance

I asked this question before but only receieved a partial answer.  
This answer gets me my value of zero.  What happens when I have a result for this query besides 1 or zero.  This answer only counts the result of the encapsulated query.   Please advise

http://www.experts-exchange.com/Microsoft/Development/MS_Access/Q_24155178.html

SELECT Count(Tbl_Early_Ontime_Late.logid) AS BidCount
FROM FiscalMonthSelector INNER JOIN Tbl_Early_Ontime_Late ON FiscalMonthSelector.[Fiscal Month Year] = Tbl_Early_Ontime_Late.MonthYear
WHERE (((Month([Report Month]))=Month(Now())) AND ((FiscalMonthSelector.Year)=Year(Now())))
GROUP BY Tbl_Early_Ontime_Late.Category
HAVING (((Tbl_Early_Ontime_Late.Category)="Early"));
0
FLRyguy
Asked:
FLRyguy
2 Solutions
 
Daniel WilsonCommented:
I don't think that SELECT COUNT(...) should ever produce a  NULL.

Where's the field that you want to show as a 0 if it's NULL?
0
 
Daniel WilsonCommented:
OK, looking at the other thread ...

SELECT iif(SUM(IIF(Tbl_Early_Ontime_Late.logid IS NULL,0,1)) is null,0, SUM(IIF(Tbl_Early_Ontime_Late.logid IS NULL,0,1))) AS BidCount
FROM FiscalMonthSelector INNER JOIN Tbl_Early_Ontime_Late ON FiscalMonthSelector.[Fiscal Month Year] = Tbl_Early_Ontime_Late.MonthYear
WHERE (((Month([Report Month]))=Month(Now())) AND ((FiscalMonthSelector.Year)=Year(Now())))
GROUP BY Tbl_Early_Ontime_Late.Category
HAVING (((Tbl_Early_Ontime_Late.Category)="Early"));

Open in new window

0
 
FLRyguyAuthor Commented:
I guess I misspoke.  It does not give me a value of zero.  I only get a blank instead of a numerical zero.  Thank you for your quick response.
greenshot-2009-02-18-15-11-46.jpg
greenshot-2009-02-18-15-12-44.jpg
0
Cloud Class® Course: CompTIA Cloud+

The CompTIA Cloud+ Basic training course will teach you about cloud concepts and models, data storage, networking, and network infrastructure.

 
FLRyguyAuthor Commented:
Still receive a blank instead of a numerical value of zero.  Thank you for trying DanielWilson
0
 
Daniel WilsonCommented:
OK, and what is the problem w/ the query you posted at the top?  That you got from the expert ... Sharath, I think ... in the other thread?  I'm not sure what problem I'm trying to solve here ...

thanks.
0
 
FLRyguyAuthor Commented:
The answer from Sharath

SELECT COUNT(BidCount) FROM (
SELECT Tbl_Early_Ontime_Late.Category,Count(Tbl_Early_Ontime_Late.logid) AS BidCount
FROM FiscalMonthSelector INNER JOIN Tbl_Early_Ontime_Late ON FiscalMonthSelector.[Fiscal Month Year] = Tbl_Early_Ontime_Late.MonthYear
WHERE (((Month([Report Month]))=Month(Now())) AND ((FiscalMonthSelector.Year)=Year(Now())))
GROUP BY Tbl_Early_Ontime_Late.Category) T1
WHERE (Category="Early");

only counts if there is a result as (1) or not a result (0)

My problem is with this part of Sharath answer:

Count(Tbl_Early_Ontime_Late.logid) AS BidCount

Whether the count is 1 or 100,000, I still receive a 1 as a value.  Not the true count calculated.  Hope this clarifies.
0
 
Daniel WilsonCommented:
I see ... how's this?

SELECT T0.Category,Count(Tbl_Early_Ontime_Late.logid) AS BidCount
FROM
   (Select Distinct Category from Tbl_Early_Ontime_Late) T0 Left Join
 FiscalMonthSelector on FiscalMonthSelector.Category = T0.Category INNER JOIN Tbl_Early_Ontime_Late ON FiscalMonthSelector.[Fiscal Month Year] = Tbl_Early_Ontime_Late.MonthYear
WHERE (((Month([Report Month]))=Month(Now())) AND ((FiscalMonthSelector.Year)=Year(Now()))) and T0.Category = 'Early'
GROUP BY T0.Category

Open in new window

0
 
SharathData EngineerCommented:
hope this helps you.
SELECT IIF(T2.BidCount_1 = 0,0,T3.BidCount)
  FROM (SELECT COUNT(BidCount) BidCount_1
          FROM (SELECT Tbl_Early_Ontime_Late.Category,Count(Tbl_Early_Ontime_Late.logid) AS BidCount
                  FROM FiscalMonthSelector 
                 INNER JOIN Tbl_Early_Ontime_Late ON FiscalMonthSelector.[Fiscal Month Year] = Tbl_Early_Ontime_Late.MonthYear
                 WHERE (((Month([Report Month]))=Month(Now())) AND ((FiscalMonthSelector.Year)=Year(Now())))
                 GROUP BY Tbl_Early_Ontime_Late.Category) T1
         WHERE (Category="Early")) T2
  LEFT JOIN (SELECT Tbl_Early_Ontime_Late.Category,Count(Tbl_Early_Ontime_Late.logid) AS BidCount
               FROM FiscalMonthSelector 
              INNER JOIN Tbl_Early_Ontime_Late ON FiscalMonthSelector.[Fiscal Month Year] = Tbl_Early_Ontime_Late.MonthYear
              WHERE (((Month([Report Month]))=Month(Now())) AND ((FiscalMonthSelector.Year)=Year(Now())))
              GROUP BY Tbl_Early_Ontime_Late.Category) T3
    ON T3.Category = "Early"

Open in new window

0
 
harfangCommented:
I don't understand what your screenshot shows. As Daniel Wilson said, a Count() does not return Null, but zero. Consider:

    select count(*) from table1 where true;

    select count(*) from table1 where false;

You cannot get a "select count(*)" to return Null. There is something else in your application that returns no records, and thus hides the listbox completely. Can you explain your setup, and what your screen-shots actually are? a query? a form in datasheet view?

I'm guessing the listbox works correctly, but not the container of the list box.

(°v°)
0
 
Daniel WilsonCommented:
I believe Sharath & I solved it.
would recommend split 23675482 and 23675827
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now