By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
449,457 Members | 1,488 Online
Bytes IT Community
+ Ask a Question
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
Share this Question
Share on Google+
2 Replies

Expert 100+
P: 1,039
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:
Expand|Select|Wrap|Line Numbers
  1. Public Function NeedsScanAt(documentType) As Date
  2.     Value = Now
  3.     Select Case documentType
  4.         Case 1
  5.             NeedsScanAt = DateAdd("h", 4, Value)
  6.         Case 2
  7.             If Hour(Value) > 10 Then
  8.                 ' code to update NeedScanAt to tomorrow at 10:00 AM
  9.             Else
  10.                 ' code to update NeedScanAt to today at 10:00 AM
  11.             End If
  12.         Case 3
  13.             NeedsScanAt = DateAdd("d", 3, Value)
  14.         Case Else
  15.             NeedsScanAt = 0
  16.     End Select
  18. 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.
Apr 27 '19 #2

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

Thanks again,
Apr 29 '19 #3

Post your reply

Sign in to post your reply or Sign up for a free account.