complex date and count query

[Judy]

I'm not sure this can be done in SQL, or if I need to go
to VB.

I'm trying to count the number of occupants in the
apartments per month, based only on knowing each client's
start and end lease dates. This information is all in one
table, TblClientInfo, fields dtmLeaseStart, dtmLeaseEnd.

So I can count the number of clients with a lease start
date per month, and subtract any who have a lease end that
month.

But how do you calculate in those who are in an active
lease but neither started nor ended that month?

--Judy

 

[John Verhagen]

Have you tried:

SELECT Count(TblClientInfo.dtmLeaseStart) AS CountOfdtmLeaseStart
FROM TblClientInfo
WHERE (((Format([dtmLeaseStart],"yyyymm"))<="200205") AND
((Format([dtmLeaseEnd],"yyyymm"))>"200205")) OR
(((Format([dtmLeaseStart],"yyyymm"))<="200205") AND
((Format([dtmLeaseEnd],"yyyymm"))=""));

This would count for May(05) 2002.