Make a query Count() return 0 instead of empty

19,943

Solution 1

Replace the Count statements with

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

I'm not a fan of the nested Iifs, but it doesn't look like there's any way around them, since DateDiff and BETWEEN...AND were not playing nicely.

To prune ItemNames without any added dates, the query block had to be enclosed in a larger query, since checking against a calculated field cannot be done from inside a query. The end result is this query:

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;

Solution 2

You can return

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

and all should be fine - would be in MS SQL Server - but I just saw you're using Access. Since I don't know Access enough, I'm not sure this will work - can you try it?

OK - glad to see there's something similar in Access (if not exactly the same as in SQL Server).

Marc

Solution 3

Even better, use Nz() e.g.

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

This will return 0 when the result is null, or count otherwise.

Note the Nz() function is part of the Access object model and therefore only available when used within the Access user interface. If you are using the Access database engine without the Access UI (from another application via OLE DB, ODBC, etc) then you will get an error, "Undefined function 'Nz' in expression".

Solution 4

On second though (without more information) I think you are doing this query very wrong....

As I said, when I try your original query I get an error "At most one record can be returned by this subquery".

This is probably a little closer to what you want

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;

Unless you are trying to get a count per Item name... in which case it's a little trickyier.

Share:
19,943
Andrew Scagnelli
Author by

Andrew Scagnelli

Professional programmer and forever learning.

Updated on June 04, 2022

Comments

  • Andrew Scagnelli
    Andrew Scagnelli almost 2 years

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

    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 great, except if there aren't any entries a column. Instead of returning a count of 0, an empty value is returned.

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

  • Andrew Scagnelli
    Andrew Scagnelli over 14 years
    I tried that with this line: Nz(Count("SELECT Source.DateAdded FROM Source WHERE Int(Date()-Source.DateAdded) BETWEEN 20 AND 44"),0) AS BTWN_20_44 (repeated for other age ranges), and each one returns "1".
  • BIBD
    BIBD over 14 years
    try removing the quotes ('"'s) from around the select statement
  • David-W-Fenton
    David-W-Fenton over 14 years
    Why not Nz(), i.e. one function call for each column instead of two?
  • Andrew Scagnelli
    Andrew Scagnelli over 14 years
    Removing the quotes gives an error of "Wrong number of arguments used with function in query expression '<query expression>'.
  • Andrew Scagnelli
    Andrew Scagnelli over 14 years
    Nz will not accept the SQL string properly, complaining about an incorrect number of arguments.
  • Alistair Knock
    Alistair Knock over 14 years
    Nz() is one of those functions only accessible to those who have smashed their head against the desk in search of it.
  • Andrew Scagnelli
    Andrew Scagnelli over 14 years
    I am trying to get a count per item name, in which case the latest query I posted works fine, although I will try your version out when I get back to work on Monday.