• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1348
  • 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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
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

Featured Post

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

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