449,457 Members | 1,488 Online
Need help? Post your question and get tips & solutions from a community of 449,457 IT Pros & Developers. It's quick & easy.

# Create MSAccess text box with due dates and times

 P: 8 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? Apr 25 '19 #1