Create MSAccess text box with due dates and times

[R1RAY]

I am in way over my head. Here’s the scenario: hours of operation are between 7:00 AM and 4:00 PM.
One type of document is to be scanned within 4 hours, another type by 10:00 AM, the 3rd type within 3 business days.
I need to create the SLA (Service Level Agreement due date and time) for each entry, depending on the type.
One problem I am having is the 4 hour docs can run over to the next day. That’s why =IIf(([Hours]=4),DateAdd("h",4,[MAIL_LOG_TIME])) does not work. Service stops at 4:00 PM. Simply adding 4 hours to may take it beyond 4:00 PM. But the remaining time starts the next day at 7:00 AM.
If documents are logged at, for example 2:30 PM. We have 1 ½ hrs until 4:00 PM, and 2 ½ remaining hrs starting at 7:00 AM the next day. If today’s [MAIL_LOG_DATE] and [MAIL_LOG_TIME] is 4/25/2019, 2:30 PM, I know the expected SLA is 4/26/2019, 9:30 AM, but how to calculate that, skipping weekends and holidays, And put in all in a Microsoft Access report text box, I have no clue
Also, there are 3 fields in the table [RECORDS_DATA] for each type: [Hours], always 4, [Specific Time], Always same day 10:00 AM, and [Business Days], always 3 business days from [MAIL_LOG_DATE] & [MAIL_LOG_TIME]. Only one of the three fields is applicable to each type.
So, I guess, a huge nested formula is necessary? Something like…
=IIf(([Hours]=4),DateAdd("h",4,[MAIL_LOG_TIME])),IIf(([Specific Time]="10:00:00 AM","10:00:00 AM",))IIf(([Business Days]=3),DateAdd("d",3,([MAIL_LOG_DATE] & " " & [MAIL_LOG_TIME])))
…but how to deal with weekends and holidays, still no clue. Can anybody help?

[Luuk]

You could (of should?) create a function in VBA (see: docs)

Where you now have a long expression ('=IIf(...')
you than can have something like =NeedsScanAt(documentType)

The function can read all those parameters like 'within 4 hours', 'at 10:00 AM' or 'within 3 business days'.

The function should also know about holidays.

A simple example to get you started might look like this:

Public Function NeedsScanAt(documentType) As Date
    Value = Now
    Select Case documentType
        Case 1
            NeedsScanAt = DateAdd("h", 4, Value)
        Case 2
            If Hour(Value) > 10 Then
                ' code to update NeedScanAt to tomorrow at 10:00 AM
            Else
                ' code to update NeedScanAt to today at 10:00 AM
            End If
        Case 3
            NeedsScanAt = DateAdd("d", 3, Value)
        Case Else
            NeedsScanAt = 0
    End Select
 
End Function

The code to update the time is left out as an exercise (or, in other words, I am nog wel enough educated in MSAccess to do that properly 😒)

Based on the input parameter the datetime is returned.

If Now=27/04/2019 09:30
NeedsScanAt(1) should return : 27-4-2019 13:30:00
NeedsScanAt(2) should return : 27-4-2019 10:00:00
NeedsScanAt(3) should return : 30-4-2019 09:30:00

I hope you do see the power which you can have, when creating a VBA-function for this kind of things.

[R1RAY]

Thanks, Luuk. This helps but I still have a ways to go to figure this one out.

Thanks again,
-ray