We help IT Professionals succeed at work.

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

FLRyguy
FLRyguy asked
on
Medium Priority
1,411 Views
Last Modified: 2013-11-28
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"));
Comment
Watch Question

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?
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

Author

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

Author

Commented:
Still receive a blank instead of a numerical value of zero.  Thank you for trying DanielWilson
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.

Author

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.
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

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts
SharathData Engineer
CERTIFIED EXPERT
Commented:
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

Commented:
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°)
I believe Sharath & I solved it.
would recommend split 23675482 and 23675827
Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.