Checking due time against now date

Question

I have a database with patient related information that must be updated every 12 hours. There are two shifts performing the updates (days & nights) and each shift must perform the task one time per patient per shift. My objective is to make it be so that any user looking at the database during their shift would be able to know if the task had been performed on that patient or not.

Since the task should be performed once each shift for each patient, the user on the day shift (6:30am - 6:30pm) needs to be able to look at the subform and and see an indication if the task was completed so they know to do it. At 6:30pm the indicator should be reset indicate to night shift that the task has not been completed on their shift yet, of course the indicator would be resolved once the task has been completed.

** For example: The current date/time is 7/12/2017 14:00 

Patient                Last timestamp           Due
Patient A             7/12/2017 03:40          True
Patient B             7/12/2017 10:40          False
Patient c             7/12/2017 11:40          False

Then, when the next shift arrives at 6:30PM, those same patients will reset

Patient                Last timestamp           Due
Patient A             7/12/2017 03:40          True
Patient B             7/12/2017 10:40          True
Patient c             7/12/2017 11:40          True

** During the night, as they perform the task

Patient                Last timestamp           Due
Patient A             7/12/2017 21:20          False
Patient B             7/12/2017 23:40          False
Patient c             7/13/2017 03:58          False

I thought a query providing the most recent timestamp on each patient would help me to determine part of the answer to this question.

SELECT [Pt-VAP_1].IntubationIDNumber, Max([Pt-VAP_1].VapDateTimeStamp) AS 
MaxOfVapDateTimeStamp, Last([Pt-VAP_1].VapIDNumber) AS LastOfVapIDNumber
FROM [Pt-VAP] INNER JOIN [Pt-VAP] AS [Pt-VAP_1] ON [Pt-VAP].VapIDNumber = 
[Pt-VAP_1].VapIDNumber
GROUP BY [Pt-VAP_1].IntubationIDNumber;

The problem I have is finding a way give this answer correctly between the hours of 7pm and 7am with the date change. Is there a way to do this?

Hopefully, my edit of the question provides a little more information needed to help me with a solution. Again, I will be more than happy to update this post if required. I am also using the following Dlookup formulas to get the values to provide some of the information I thought would be needed.

Any help would be greatly appreciated.

vapCK = DLookup("[VapCharted]", "[03-LastVapCharting]", "[IntubationIDNumber]=" & [IntIDNo])
vapDTStamp = DLookup("[MaxOfVapDateTimeStamp]", "[03-MaxDateVap]", "[IntubationIDNumber]=" & [IntIDNo])
vapLastRCP = DLookup("[Last RCP]", "[03-LastVapCharting]", "[IntubationIDNumber]=" & [IntIDNo])

Answer 1

If I understood your problem correctly, you could try something like this.

Get the date-time it was last updated, calculate the next update due by adding 12hrs to the last update and compare it to Now().

Public Function IsUpdateRequired() As Boolean

    Dim lastUpdatedOn As Date
        lastUpdatedOn = #7/11/2017 6:30:00 AM#  'DLookup("DateLastUpdated", "TableName")

    Dim nextUpdateOn As Date
        nextUpdateOn = DateAdd("h", 12, lastUpdatedOn)

    IsUpdateRequired = (nextUpdateOn <= Now())
End Function

In compact form:

Public Function IsUpdateRequired() As Boolean
    IsUpdateRequired = (DateAdd("h", 12, DLookup("DateLastUpdated", "TableName")) <= Now())
End Function