I have an admission table in my database that stores the admission date (EPI_START_DATE) and the discharge date (EPI_END_DATE) as well as other fields for a mental health hospital. The unique id is the patients DMHDD_ID and their admission date. I am trying to calculate their length of stay in the community between admissions. Here is the code I wrote to try and do this (I am also attaching the database). I am not very good with code so if it seems a little amateur that is why. Call the function as one of the query fields for "qryData". This code works (kinda) except that it gives the LOS (length of stay) of their last record for every one of the records. I guess what I am asking is how do I make the correct varLOS go to the correct record in the query? Hope this makes sense. Any help would be much appreciated.

Public Function LOSCom() As Integer

Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim varRecords As Integer
Dim varLstAdm As Date
Dim varPrevDisch As Date
Dim varReAdmit As Integer
Dim varCount As Integer
Dim varCounter As Integer

Set db = CurrentDb()
Set rst = db.OpenRecordset("qryEpisodeData")

varCount = 1
rst.MoveLast
varRecords = rst.RecordCount
Debug.Print varRecords
rst.MoveFirst


Do Until varCount > varRecords - 1
varCount = varCount + 1
varLstAdm = rst!EPI_START_DATE
Debug.Print varLstAdm
rst.MoveNext
varPrevDisch = rst!EPI_END_DATE
Debug.Print varPrevDisch
varReAdmit = DateDiff("d", varPrevDisch, varLstAdm)
Debug.Print varReAdmit
LOSCom = varReAdmit
Loop


End Function