Make a request Count () return 0 instead of empty

Question

I have a report that tracks how long some items have been in the database, and does this by tracking it in a series of age ranges (20-44, 45-60, 61-90, 91-180, 180+). I have the following query as a report data source:

SELECT DISTINCT Source.ItemName, 
Count(SELECT Source.DateAdded FROM Source WHERE Int(Date()-Source.DateAdded) > 20) AS Total, 
Count(SELECT Source.DateAdded FROM Source WHERE Int(Date()-Source.DateAdded) BETWEEN 20 AND 44) AS BTWN_20_44, 
Count(SELECT Source.DateAdded FROM Source WHERE Int(Date()-Source.DateAdded) BETWEEN 45 AND 60) AS BTWN_45_60, 
Count(SELECT Source.DateAdded FROM Source WHERE Int(Date()-Source.DateAdded) BETWEEN 61 AND 90) AS BTWN_61_90, 
Count(SELECT Source.DateAdded FROM Source WHERE Int(Date()-Source.DateAdded) BETWEEN 91 AND 180) AS BTWN_91_180, 
Count(SELECT Source.DateAdded FROM Source WHERE Int(Date()-Source.DateAdded) > 180) AS GT_180
FROM Source
GROUP BY Source.ItemName;

This query works fine unless there are no entries in the column. Instead of returning the number 0, an empty value is returned.

How do I get Count () to return 0 instead of empty?

Answer 1

Replace operators Countwith

Sum(Iif(DateDiff("d",DateAdded,Date())>=91,Iif(DateDiff("d",DateAdded,Date())<=180,'1','0'),'0')) AS BTWN_91_180,

Iif s, , DateDiff BETWEEN...AND .

ItemName - , , . :

SELECT *
FROM 
     (
     SELECT DISTINCT Source.ItemName AS InvestmentManager, 
     Sum(Iif(DateDiff("d",DateAdded,Date())>=20,Iif(DateDiff("d",DateAdded,Date())<=44,'1','0'),'0')) AS BTWN_20_44,
     Sum(Iif(DateDiff("d",DateAdded,Date())>=45,Iif(DateDiff("d",DateAdded,Date())<=60,'1','0'),'0')) AS BTWN_45_60,
     Sum(Iif(DateDiff("d",DateAdded,Date())>=61,Iif(DateDiff("d",DateAdded,Date())<=90,'1','0'),'0')) AS BTWN_61_90,
     Sum(Iif(DateDiff("d",DateAdded,Date())>=91,Iif(DateDiff("d",DateAdded,Date())<=180,'1','0'),'0')) AS BTWN_91_180,
     Sum(Iif(DateDiff("d",DateAdded,Date())>180,'1','0')) AS GT_180,
     Sum(Iif(DateDiff("d",DateAdded,Date())>=20,'1','0')) AS Total
     FROM Source
     WHERE CompleteState='FAILED'
     GROUP BY ItemName
     )
WHERE Total > 0;

Answer 2

ISNULL(Count(......), 0)

- MS SQL Server, , Access. Access , , - ?

- - Access ( , SQL Server).

Answer 3

, Nz(), .

Nz(Count(SELECT Source.DateAdded 
            FROM Source 
            WHERE Int(Date()-Source.DateAdded), 0)

0, .

, Nz() Access Access. Access ( OLE DB, ODBC ..), "Undefined function" Nz " ".

Answer 4

, ( ), , ....

, , " ".

, , ,

SELECT DISTINCT Source.ItemName, 
    (SELECT count(Source.DateAdded ) FROM Source 
        WHERE Int(Date()-Source.DateAdded)> 20)  AS Total, 
    (SELECT count(Source.DateAdded ) FROM Source  
        WHERE Int(Date()-Source.DateAdded) BETWEEN 20 AND 44) AS BTWN_20_44, 
    (SELECT count(Source.DateAdded ) FROM Source  
        WHERE Int(Date()-Source.DateAdded) BETWEEN 45 AND 60) AS BTWN_45_60, 
    (SELECT count(Source.DateAdded ) FROM Source  
        WHERE Int(Date()-Source.DateAdded) BETWEEN 61 AND 90) AS BTWN_61_90, 
    (SELECT count(Source.DateAdded ) FROM Source  
        WHERE Int(Date()-Source.DateAdded) BETWEEN 91 AND 180) AS BTWN_91_180, 
    (SELECT count(Source.DateAdded ) FROM Source  
        WHERE Int(Date()-Source.DateAdded) > 180) AS GT_180
FROM Source
GROUP BY Source.ItemName;

If you are not trying to get a name for each name ... in this case it is a little more complicated.