[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

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

Posted on 2009-02-18
9
Medium Priority
?
1,271 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
0
Comment
Question by:FLRyguy
9 Comments
 
LVL 39

Expert Comment

by:BrandonGalderisi
ID: 23673599
if nz(SomeField, 0) is not working, try...

iif(SomeField is null,0,SomeField)
0
 
LVL 75
ID: 23673625
For that field in your Row Source (not listed btw) ... use thisL

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

mx
0
 

Author Comment

by:FLRyguy
ID: 23674248
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
Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

 
LVL 41

Expert Comment

by:Sharath
ID: 23674315
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
 

Author Comment

by:FLRyguy
ID: 23674401
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
 
LVL 41

Accepted Solution

by:
Sharath earned 1000 total points
ID: 23674577
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
 

Author Closing Comment

by:FLRyguy
ID: 31548412
You are amazing!  Thank you for the guidance.  I can apply this to many other queries in my database.  Have a great afternoon!!
0
 

Author Comment

by:FLRyguy
ID: 23674701
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
 
LVL 41

Expert Comment

by:Sharath
ID: 23675675
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

Featured Post

Configuration Guide and Best Practices

Read the guide to learn how to orchestrate Data ONTAP, create application-consistent backups and enable fast recovery from NetApp storage snapshots. Version 9.5 also contains performance and scalability enhancements to meet the needs of the largest enterprise environments.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
Windows Explorer let you handle zip folders nearly as any other folder: Copy, move, change, and delete, etc. In VBA you can also handle normal files and folders, but zip folders takes a little more - and that you'll find here.
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

831 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question