DateAdd (excluding weekends) depending on criteria in another field

[a1_robs via AccessMonster.com]

Hi All,

I know some of your guys and gals out there like a challenge, so how about
this one for you!

I am creating a database to record maintenance requests that come in from
customers from four buildings. The three tables (with their fields in
brackets) are:

"Callers" - List of callers (CallerID, FirstName, Surname, ExtNo, Email)
"Priorities" - Priority level for jobs and the expected response times
(Priority, AddDays, AddHours)
"ReactiveMaint" - List of maintenance requests (CallNo, Time, Date, CallerID,
Location, Priority, Problem, CompletedDate, CompletedTime).

What I need to do is work out when a 'call' is due, based on the Time and
Date it was logged, depending on the Priority level it was allocated.

Priority levels are as follows:

Priority 1 = 0 Days, 2 hours
Priority 2 = 0 Days, 4 Hours
Priority 3 = 1 Day
Priorty 4 = 5 Days
Priority 5 = 30 Days

The form I use to log the calls is called "CallLogger", and whose control
source is from a query called ReactiveMaintAll (which links all three tables).
The 'Priority' for each call is allocated via a combo box in "CallLogger"
whose row source is the "Priorities" table, which looks like this:

Priority AddDays AddHours
1 0 1
2 0 4
3 1 0
4 5 0
5 30 0

To get me going, I have put an unbound text box in the form "CallLogger" with
the following code:

=DateAdd("w",[AddDays],[Date])

This works well for just adding the number of days to a call. The problem is
that I need to exclude weekends (not worried about holidays), and also work
out the time due if it is a Priority 1 or 2.

If anyone can figure out a way to do this, I will love you forever and will
worship the ground you walk on.

Rob.

 

[Tom van Stiphout]

On Wed, 18 Mar 2009 11:37:15 GMT, "a1_robs via AccessMonster.com"

That's too good a proposition to pass up
http://www.mvps.org/access/datetime/date0012.htm

-Tom.
Microsoft Access MVP

 

[a1_robs via AccessMonster.com]

Hi Tom,

Thanks for the link - I have had a look at this previously, but it doesn't
really help me too much as it doesn't give me an example of how this would
work with the variable 'Priority' field.

I'm afraid that I am not worshipping you just yet!!!

Rob.

 

[Tom van Stiphout]

On Wed, 18 Mar 2009 14:23:20 GMT, "a1_robs via AccessMonster.com"

Now *I* am disappointed. They rarely hand out silver platters in this
or any other newsgroup. That's the realm of the Commercial Services
department. Rather they have great respect for people who really try
to make it work and then need a hand to get to the finish line.

Here is hoping you can meet me 95% of the way.

-Tom.
Microsoft Access MVP

 

[a1_robs via AccessMonster.com]

Sorry Tom, you have lost me there. Is there anyone else out there who can
help?

 

[John W. Vinson]

Sorry Tom, you have lost me there. Is there anyone else out there who can
help?

If by "Help" you mean having someone write a complete, customized solution
which you can simply copy into your database and run with, that's a bit
unreasonable for free, unpaid volunteer support.

If by "Help" you mean providing you with the information to let you
understand and create the solution yourself, Tom's done that.

If you would like a refund of the fee you paid for this newsgroup service,
here you go: [ ]

 

[a1_robs via AccessMonster.com]

Wow thanks for your amazing advice guys. I am amazed with your technical
ability. Look I can now post links too!!!!

http://www.accessmonster.com/Uwe/Forum.aspx/access-queries/30716/DateAdd

See, there are people out there that are willing to help us simpeltons that
can't create loads of meaningful code.

 

[a1_robs via AccessMonster.com]

Just trying to prove a point

 

[a1_robs via AccessMonster.com]

How come you gave him so much assistance John?! I would be more than happy
to pay $50 to your nominated charity if you can assist me - and I would still
worship the ground you walk on.

PS - I'm not getting paid for creating this database, just trying to help out
my team that are struggling with the current system by creating a custom
database for them.

 

[Michael Gramelspacher]

How come you gave him so much assistance John?! I would be more than happy
to pay $50 to your nominated charity if you can assist me - and I would still
worship the ground you walk on.

PS - I'm not getting paid for creating this database, just trying to help out
my team that are struggling with the current system by creating a custom
database for them.

Back to your problem.

Assume work hours of 08:00 - 17:00.

If you get a priority 1 call at 16:00 today (Friday), when is the due date and time?

Say it is a priority 3 call, now when is the due date and time?

These kinds of problems are not trival and require more time than many volunteer's have to give.

 

[a1_robs via AccessMonster.com]

No I really do appreciate any help that anyone can give me - and I mean that
genuinely.

The due date for a Priority 1 call at 16:00 today (Friday), would be due at
17:00 today

The due date for a Priority 3 call at 16:00 today (Friday), would be due at
16:00 next Friday

Thanks,

Rob.

 

[a1_robs via AccessMonster.com]

Sorry Michael,

Just noticed a slight typo on my original post.

Priority 1 should be 0 Days, 1 Hours (not 2 Hours as I typed!)

Rob.

 

[Michael Gramelspacher]

Sorry Michael,

Just noticed a slight typo on my original post.

Priority 1 should be 0 Days, 1 Hours (not 2 Hours as I typed!)

Rob.

This is not a ready-made solution, but it is something to play with.
It uses the GetBusinessDay function by Arvin Meyer, which you can google for.
The function requires a tblHolidays, which has one column HolidayDate
Note that this has not really been tested.

Function GetCallDueDate() As Date

Dim CallDate As Date ' date call received
Dim CallTime As Date ' time call received
Dim AddHours As Integer ' hours to add
Dim AddDays As Integer ' days to add
Dim StartTime As Date ' start time of business day
Dim QuitTime As Date ' quit time of business day

CallDate = #3/20/2009#
CallTime = #4:00:00 PM#
AddHours = 0
AddDays = 5
StartTime = #8:00:00 AM#
QuitTime = #5:00:00 PM#

If AddHours > 0 Then
If DateDiff("n", DateAdd("h", AddHours, CallTime), QuitTime) < 0 Then
GetCallDueDate = DateAdd("n", (DateDiff("n", DateAdd("h", AddHours, _
CallTime), QuitTime)) * -1, GetBusinessDay(CallDate, 1) + StartTime)
Else
GetCallDueDate = DateAdd("h", AddHours, CallDate + CallTime)
End If
Else
If AddDays > 0 Then
GetCallDueDate = GetBusinessDay(CallDate, AddDays) + CallTime
End If
End If

End Function

?GetCallDueDate
3/27/2009 4:00:00 PM