I realize that I'm coming in a little late but it's an interesting problem. What about this:
SELECT
a.clientid,
a.status,
a.effdate AS startdate,
MIN(b.effdate) AS enddate,
DATEDIFF(day, a.effdate, ISNULL(MIN(b.effdate), GETDATE())) AS length
FROM
status a
LEFT OUTER JOIN status b ON a.clientid = b.clientid AND b.effdate > a.effdate
GROUP BY
a.clientid,
a.status,
a.effdate
ORDER BY 1, 3
I had the Query Analyzer open so the query's written in TSQL. I can't think of any reason that it's wouldn't work in VFP with a little tweaking.
-Mike