Question Regarding Dates in a Query

[NtvTxn72]

I have a query set up that shows the admit date of a patient and the date that their care plan is supposed to be initially reviewed. The initial review occurs 28 days after admission. After that initial date, they are reviewed every 84 days. I know how to generate the date for the 84-day review (IDT Date), but what I am wanting to know is if there is a way to for the date to automatically update when the date has past. For example, Jane Doe was admitted on 05/16/2012, her initial date would be 06/13/2012, and I want to know what date she is to be reviewed currently without having to create a billion calculated columns.



We are currently keeping this data in a spreadsheet and the issue is that we have inexperienced and unteachable individuals that are messing up the dates, so I thought that it would be easier to use the existing database that holds all of our patient data in it.

[June7]

Ooops not gonna cut it. content deleted.

[NtvTxn72]

I tried anyway. LOL

I do believe it is going to be VBA. I have scoured the internet, but haven't found anything yet, but it is also difficult to search for something like this.

[orange]

I think this is the logic required. This shows you the date of the first 10 review visits.
You could make it a function. It depends on exactly what you are trying to do.

'---------------------------------------------------------------------------------------
' Procedure : patientVisits
' Author    : mellon
' Date      : 04-May-2017
' Purpose   :The initial review occurs 28 days after admission.
'After that initial review date, they are reviewed every 84 days.
'---------------------------------------------------------------------------------------
'
Sub patientVisits()

    Dim rs As DAO.Recordset
    Dim db As DAO.Database
    Dim i As Integer
    Dim AdmitDate As Date: AdmitDate = #5/16/2012#
10  On Error GoTo patientVisits_Error

20  Dim InitialReview As Integer: InitialReview = 28
30  Dim FollowUpReview As Integer: FollowUpReview = 84
40  Dim NumReviews As Integer: NumReviews = 10
50  For i = 1 To NumReviews
60      If i = 1 Then
70          Debug.Print "DateReview-" & i & " " & DateAdd("d", InitialReview, AdmitDate)
80      Else
90          Debug.Print "DateReview-" & i & " " & DateAdd("d", (FollowUpReview * i), AdmitDate)
100     End If
110 Next i
patientVisits_Exit:
120 Exit Sub

patientVisits_Error:
130 MsgBox "Error " & err.number & " in line " & Erl & " (" & err.Description & ") in procedure patientVisits of Module AWF_Related"
140 Resume patientVisits_Exit

End Sub

Results:
DateReview-1 13-Jun-2012
DateReview-2 31-Oct-2012
DateReview-3 23-Jan-2013
DateReview-4 17-Apr-2013
DateReview-5 10-Jul-2013
DateReview-6 02-Oct-2013
DateReview-7 25-Dec-2013
DateReview-8 19-Mar-2014
DateReview-9 11-Jun-2014
DateReview-10 03-Sep-2014

[June7]

Here's what I was trying to do in my earlier (content deleted now) post by a single expression in query.
Going forward from the current date, to determine the next review date, try:

Function GetReviewDate(dteAdmit As Date) As Date
Dim x As Integer
x = DateDiff("d", dteAdmit, Date)
If x <= 28 Then
    GetReviewDate = dteAdmit + 28
Else
    x = Int((x - 28) / 84) + IIf((x - 28) Mod 84 > 0, 1, 0)
    GetReviewDate = x * 84 + 28 + dteAdmit
End If
End Function

It probably could be one long IIf() but might be too long for query as there is a limit to how many characters can be in an expression.