Re: count fun returns with no data, how can i get it to return zer

I'm pretty sure that the nature of the group by clause is such that it
returns nothing if there are no records. To get it to return a record with a
zero count, you'll need to create a new table or query that you can join with
your existing ClientCount query using an Outer join.

One way to do this is to create a separate table (eg. "SearchTable") with
fields "Status" and "Effective Date", and put one record in it with Status =
Transitioned and Effective Date = 10/1/2005

The SQL to join this to your ClientCount query is:
SELECT SearchTable.Status, SearchTable.[Effective Date], ClientCount.
[CountOfClient Name]
FROM SearchTable LEFT JOIN ClientCount ON (SearchTable.[Effective Date] =
ClientCount.[Effective Date]) AND (SearchTable.Status = ClientCount.Status);

Note that this returns a null rather than a zero count, but you can deal with
that using an IIF expression.

Also, if you do it this way, you won't need the HAVING clause in your
ClientCount query.

The messy part about this is that you need to add a record to the table for
every Status / Date combination you want a count for. Whether this is what
you want depends on whether you want to enter all of the criteria up front,
or only when the query runs - it's not clear to me from your SQL how the user
interface will work, because you've got the criteria in hard coded into the
SQL...

--
Message posted via http://www.accessmonster.com
.