I have created a simple form with unbound text boxes to work out the solution the code attached to a command button prompts the correct Out Bound Start time:
Code:
Private Sub Command16_Click()
'This code deals with the basic concept of the time range comparision. This code
'verifies that both tCodeStart and tCodeEnd is not in the range of tOutStart or tOutEnd
'If so the Outbound Time starts at tCodeEnd Time otherwise it will start at tOutStart Time
Dim tCodeStart As Date 'CodeStartTime
Dim tCodeEnd As Date 'CodeEndTime
Dim tOutStart As Date 'OutBoundStart Time Default
Dim tOutEnd As Date 'OutBoundStartTime + OutBoundDuration=OutBoundEndTime
tCodeStart = Format(Me.Text4, "Short Time")
tCodeEnd = Format(Me.Text6, "Short Time")
tOutStart = Format(Me.Text8, "Short Time")
tOutEnd = Format(DateAdd("n", Me.Text10, tOutStart), "Short Time")
MsgBox tOutEnd
Me.Text12 = tOutEnd
If tCodeStart >= tOutStart And tCodeStart <= tOutEnd Or tCodeEnd >= tOutStart And tCodeEnd <= tOutEnd Then
MsgBox "Out Bound Time Start: " & tCodeEnd
Else
MsgBox "Out Bound Time Start: " & tOutStart
End If
End Sub
The assumption here is that the CodeStartTime and CodeEndTime Range if falls within the OutBoundTime Range the OutBoundStartTime will end of CodeEndTime.
Lets see some Test Results:
If CodeStartTime is : 9:45 CodeEndTime is : 10:00 OutBoundStartTime: 9:00 and OutBound Range= 60 min OutBoundEndTime: 10:00
Result: OutBoundStartTime: 10:00
If CodeStartTime is : 9:02 CodeEndTime is : 09:17 OutBoundStartTime: 9:00 and OutBound Range= 60 min OutBoundEndTime: 10:00
Result: OutBoundStartTime: 9:17
If CodeStartTime is : 9:02 CodeEndTime is : 09:17 OutBoundStartTime: 9:00 and OutBound Range= 60 min OutBoundEndTime: 10:00
Result: OutBoundStartTime: 9:17
If CodeStartTime is : 10:15 CodeEndTime is : 10:30 OutBoundStartTime: 9:00 and OutBound Range= 60 min OutBoundEndTime: 10:00
Result: OutBoundStartTime: 9:00
If CodeStartTime is : 9:00 CodeEndTime is : 9:15 OutBoundStartTime: 9:00 and OutBound Range= 60 min OutBoundEndTime: 10:00
Result: OutBoundStartTime: 9:15
You can use this to create a calculative Field to show the OutBoundStartTime
Code:
Option Compare Database
Option Explicit
Dim tCodeStart As Date
Dim tCodeEnd As Date
Dim tOutStart As Date
Dim tOutEnd As Date
Dim tOutRange As Integer
Function outBoundTime(tCodeStart, tCodeEnd, tOutStart, tOutRange) As Date
'Syntax: outBountTime([CodeStartTime],[CodeEndTime],[OutBoundStartTime],[OutBoundDuration in Minutes])
'All Time are in 24 Hours Format and OutBoundDuration is integer
tOutEnd = Format(DateAdd("n", tOutRange, tOutStart), "Short Time")
If tCodeStart >= tOutStart And tCodeStart <= tOutEnd Or tCodeEnd >= tOutStart And tCodeEnd <= tOutEnd Then
outBoundTime = tCodeEnd
Else
outBoundTime = tOutStart
End If
End Function