This problem was solved in SQL as below.
How to convert it to Access vba?
CREATE TABLE dbo.PatientInfo (
MEMBID CHAR(8),
ADMDATE DATE,
DSCHDATE DATE
);
go
INSERT INTO dbo.PatientInfo
VALUES ('06000248','08/02/2010','08/04/2010'),
('06000248','08/10/2010','08/12/2010'),
('06000248','08/20/2010','08/22/2010'),
('06000348','08/02/2011','08/04/2011'),
('06000348','08/10/2011','08/12/2011'),
('06000348','08/20/2011','08/22/2011');
GO
WITH BaseData AS (
SELECT
ROW_NUMBER() OVER (PARTITION BY MEMBID ORDER BY DSCHDATE) rn,
MEMBID,
ADMDATE,
DSCHDATE
FROM
dbo.PatientInfo
)
SELECT
bd1.MEMBID,
bd1.ADMDATE,
bd1.DSCHDATE,
ISNULL(DATEDIFF(dd, bd2.DSCHDATE, bd1.ADMDATE),0) DaysOut
FROM
BaseData bd1
LEFT OUTER JOIN BaseData bd2
ON (bd1.rn = bd2.rn + 1
AND bd1.MEMBID = bd2.MEMBID)
;
GO
DROP TABLE dbo.PatientInfo;
GO
-----
MEMBID--ADMDATE----DSCHDATE-DaysOut
06000248 2010-08-02 2010-08-04 0
06000248 2010-08-10 2010-08-12 6
06000248 2010-08-20 2010-08-22 8
06000348 2011-08-02 2011-08-04 0
06000348 2011-08-10 2011-08-12 6
06000348 2011-08-20 2011-08-22 8