Simple Events Management -- Either Here or Away

[robboll]

I am trying to set up a VBA routine that counts the number of days
HERE vs AWAY. The sample events start (e.g., a_s) and end (e.g.,
a_e) at random dates within the period: July 1 thru Aug 6

All event periods are "AWAY" the rest are considered "HERE"

Sample Events:
a_s: 5-Jul
a_e: 21-Jul
c_s: 5-Jul
c_e: 21-Jul
e_s: 6-Jul
e_e: 24-Jul
g_s: 4-Jul
g_e: 20-Jul
i_s: 6-Jul
i_e: 20-Jul
k_s: 26-Jul
k_e: 4-Aug

Graphing these dates out (the hard way) for the period July 1 thru Aug
6 I come up with:

AWAY: 31 DAYS
HERE: 6 DAYS

Is there a VBA routine or an array that can handle this routine? Any
help greatly appreciated!!!

RBollinger

 

[JString]

This function may come in handy:

datediff("d",date_start,date_end)

 

[robboll]

I am trying to set up a VBA routine that counts the number of days
HERE vs AWAY.  The sample events start  (e.g., a_s) and end (e.g.,
a_e) at random dates within the period:  July 1 thru Aug 6

All event periods are "AWAY"   the rest are considered "HERE"

Sample Events:
a_s: 5-Jul
a_e: 21-Jul
c_s: 5-Jul
c_e: 21-Jul
e_s: 6-Jul
e_e: 24-Jul
g_s: 4-Jul
g_e: 20-Jul
i_s: 6-Jul
i_e: 20-Jul
k_s: 26-Jul
k_e: 4-Aug

Graphing these dates out (the hard way) for the period July 1 thru Aug
6 I come up with:

AWAY: 31 DAYS
HERE: 6 DAYS

Is there a VBA routine or an array that can handle this routine?  Any
help greatly appreciated!!!

RBollinger

I forgot to mention that the start and stop dates for all events are
all on the same row like: member, a_s, a_e, c_s, c_e, e_s, e_e, etc.

I thought about datediff but didn't know how to apply it to this
problem.

 

[robboll]

Explaining in more detail. I read my original explanation and it
sounded a little confusing. So . . .

I forgot to mention that the start and stop dates for all events are
all on the same row like:  member, a_s, a_e, c_s, c_e, e_s, e_e, etc.

I thought about datediff but didn't know how to apply it to this
problem.

The routine should see that events a,c,e,g, and I overlap, and that
event g (July 4) is the earliest of the overlapping events. Event e
(July 24) is the latest of the overlapping events accounting for 21
days. It should also see that event k (July 26 thru August 4)
accounts for 10 days. Total Event days: 31 (Away)

Total days of period Jul 1 thru Aug 6 = 37 days
37 - 31 (Away) = 6 (Here)

I hope this clears up what I am trying to do at least conceptually.
Appreicate any help with this!

RBollinger

 

[JString]

Well let me see if I'm getting what it is that you're trying to do. It looks
like these events are travel schedules, am I right? But if so how can that
work out of these events are overlapping? After all a person can only be in
one place at once.

Regardless, it may be easier to reorganize your table unless the number of
events always remains the same. For example, you could create a new table
just for events, linked to the main table through a one-to-many relationship
using the 'member' field, with two additional fields for event_start and
event_end. That way you can create an unlimited amount of events for each
user, with each record in the new table representing a single event.

If that will work for you, you could then build a query that pulls all event
records for a specific member that fit within a specific date range. From
that point, the recordset could be processed fairly easily with a function to
calculate the days here.

Now if the number of events per member is always going to be the same then
that might all be a waste of effort. You can still build a function in VBA
to perform the calculation that you need, but you will probably run into
problems any time you change the number of fields.

 

[robboll]

Well let me see if I'm getting what it is that you're trying to do.  Itlooks
like these events are travel schedules, am I right?  But if so how can that
work out of these events are overlapping?  After all a person can only be in
one place at once.  

Regardless, it may be easier to reorganize your table unless the number of
events always remains the same.  For example, you could create a new table
just for events, linked to the main table through a one-to-many relationship
using the 'member' field, with two additional fields for event_start and
event_end.  That way you can create an unlimited amount of events for each
user, with each record in the new table representing a single event.  

If that will work for you, you could then build a query that pulls all event
records for a specific member that fit within a specific date range.   From
that point, the recordset could be processed fairly easily with a function to
calculate the days here.

Now if the number of events per member is always going to be the same then
that might all be a waste of effort.  You can still build a function inVBA
to perform the calculation that you need, but you will probably run into
problems any time you change the number of fields.









- Show quoted text -

JString. These aren't travel schedules. They are case numbers that
are sent to various departments while the case is open. Because the
departments do completely different actions they can be sent to
multiple departments at the same time. So if we send it to
departments a, c, e the same day, the "Away" time will be until the
last department returns it. The AWAY time is subtracted from the
total time the case is open to determine the "HERE" time.

I don't expect cases to be forwarded to all departments at the same
time, explaining the overlapping. In that case the AWAY time would
start with the when the case is forwarded to the first department. And
end when the last department returns it. I knew it could be somewhat
perplexing so included the model.
And I really appreciate your help,

RBollinger

 

[JString]

Okay, then in that case I'm guessing that you won't be adding any new date
groups unless your company actually reorganizes and the number of departments
change for some reason, am I right? If so then give this function a try:

Private Function dateCount(start_date As Date, end_date As Date, Optional
mode = "away") As Integer
On Error GoTo ErrHandler
Dim strDateFields(2) As Variant
Dim intdRange As Integer
Dim d As Integer
Dim i As Integer
Dim dateCompare As Date
Dim intAwayCount As Integer

strDateFields(0) = Array("a_s", "c_s", "e_s", "g_s", "i_s", "k_s")
strDateFields(1) = Array("a_e", "c_e", "e_e", "g_e", "i_e", "k_e")

intdRange = DateDiff("d", start_date, end_date)
For d = 0 To intdRange
dateCompare = start_date + d
For i = 0 To UBound(strDateFields(0))
If dateCompare >= Me.Controls(strDateFields(0)(i)) Then
If dateCompare <= Me.Controls(strDateFields(1)(i)) Then
intAwayCount = intAwayCount + 1
Exit For
End If
End If
Next i
Next d

If mode = "here" Then
dateCount = DateDiff("d", start_date, end_date) + 1 - intAwayCount
ElseIf mode = "away" Then
dateCount = intAwayCount
Else: GoTo ErrHandler
End If

Exit Function
ErrHandler:
MsgBox "dateCount Error." & vbCr & vbCr & Err.Description, , "Error"
End Function

 

[robboll]

Thank you. This is really awsome code.

RBollinger

 

[JString]

No problem. I tested it and it returns the same values you came up with your
manual calculations so it should work. Just let me know if you need help
getting it to work for your app.

 

[robboll]

Yes! I did try it out and it did come back with the correct number.
Really imprressive work using arrays.