Can a Query Return a Value anyway??? 1

[orna]

this query is part of a union query:

SELECT "<10" AS range, Count(*) AS [Count]
FROM C LEFT JOIN A ON C.NUMB = A.NUMB
WHERE DateDiff("d",[C].[InsertDate],[CLOSEDATE]<=10
AND A.UNIT_CODE = Me!UNIT
GROUP BY 1;

is there a way to force the query to return zero as count
when no records meet the cretaria?
(i send this query to an excel chart and need all columns
to have value)

TIA

 

[r937]

select '<10' AS range
     , sum(
        iif(DateDiff("d"
                    ,C.InsertDate
                    ,CLOSEDATE) <= 10
        and A.UNIT_CODE = Me!UNIT
          , 1, 0)
          )  AS [Count]
  from C 
left outer
  join A 
    on C.NUMB = A.NUMB

rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (next course starts March 6 2005)

 

[orna]

Thanks rudy!!!