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

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
FLRyguyAsked:
Who is Participating?
 
SharathData EngineerCommented:
try this.
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");

Open in new window

0
 
BrandonGalderisiCommented:
if nz(SomeField, 0) is not working, try...

iif(SomeField is null,0,SomeField)
0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
For that field in your Row Source (not listed btw) ... use thisL

Expr1: IIF (Nz([YourField],"") = "", 0, [YourField])

mx
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
FLRyguyAuthor Commented:
Below is my row source.  I will try your suggestions.  Thank you

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
 
SharathData EngineerCommented:
try this.
SELECT 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:
Still not showing zero values but rather a blank in my list box and query result.
greenshot-2009-02-18-15-11-46.jpg
greenshot-2009-02-18-15-12-44.jpg
0
 
FLRyguyAuthor Commented:
You are amazing!  Thank you for the guidance.  I can apply this to many other queries in my database.  Have a great afternoon!!
0
 
FLRyguyAuthor Commented:
Well 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.
0
 
SharathData EngineerCommented:
In the encapsulated query, you will be having each Category and the count of LogId. If your search Category is not present, you want it to display 0 rather than no records.
My Count in the main SELECT won't harm you as it will give you the same count number for a Category. If the Category doesn't present, it will give you zero as count is an aggregate function. Try with an existing Category in the WHERE clause and check you are getting the same count.
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.

All Courses

From novice to tech pro — start learning today.