Make a query Count() return 0 instead of empty
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 Iif
s, but it doesn't look like there's any way around them, since DateDiff
and BETWEEN...AND
were not playing nicely.
To prune ItemName
s 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.
Comments
-
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 over 14 yearsI 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 over 14 yearstry removing the quotes ('"'s) from around the select statement
-
David-W-Fenton over 14 yearsWhy not Nz(), i.e. one function call for each column instead of two?
-
Andrew Scagnelli over 14 yearsRemoving the quotes gives an error of "Wrong number of arguments used with function in query expression '<query expression>'.
-
Andrew Scagnelli over 14 years
Nz
will not accept the SQL string properly, complaining about an incorrect number of arguments. -
Alistair Knock over 14 yearsNz() is one of those functions only accessible to those who have smashed their head against the desk in search of it.
-
Andrew Scagnelli over 14 yearsI 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.