Check if time falls between value

[Sunny]

Hi,

I need some help with this. I have an access form, which will capture
hours worked under each category, so:

If I was at "Cash" from 09:00 - 12:00

the form should not allow me to submit saying I was at

"Maintenance" from 08:00-11:00, because there would be a duplication of
09:00-11:00 which is invalid.

How would I include the validation code?

Thanks
Sunil

 

[Albert D. Kallal]

The trick in a booking system, or your cases
is to simply prevent a collison

And, to prevent collisions, the logic here is quite simple:


A collision occurs when:


RequestStartDate <= EndDate
and
RequestEndDate >= StartDate


The above is thus a rather simply query, but if any collision occurs, the
above will return records..and you simply don't allow the booking. In other
words, since we NEVER allow booking with a collision, then the above simply
statement will work for us.


dim strWhere as string
dim dtRequeestStartDate as date
dim dtRequestEndDate as date


dtRequestStartDate = inputbox("Enter start Date")
dtRequestEndDate = inputbox("Enter end date")


strWhere="#" & format(dtRequestStartDate,"mm/­dd/yyyy") & "# <= EndDate" & _
" and #" & format(dtRequestEndDate,"mm/dd­/yyyy") & "# >= StartDate"


if dcount("*","tableBooking",strW­here) > 0 then
msgbox "sorry, you can't book
....bla bla bla....


The above is just an example, but does give yo the idea. For the extra
condiations "such as cash" etc, just add them to the critera....

 

[Guest]

Sunil:

The following function is one used to do this in an application I developed
a few years ago. It works with an unbound form in which the date, hours and
minutes are entered into separate controls and the actual date/time values
are then computed. The function takes the true date/time values as its
arguments, however, so the logic behind it would be equally applicable to a
bound form with controls bound to true date/time fields, calling the function
in the form's BeforeUpdate event procedure:

Private Function ValidateTimes(dtmStartTime As Date, dtmEndTime As Date) As
Boolean

Dim strMessage As String, strCriteria As String
Dim varTimes As Variant

ValidateTimes = False

varTimes = Me!cboStartHour + Me!cboStartMinutes + _
Me!cboEndHour + Me!cboEndMinutes

' execute only if all time controls have values
If Not IsNull(varTimes) Then
' validate that start/end times are compatible
If dtmStartTime >= dtmEndTime Then
strMessage = "Finishing time must be later than start time."
MsgBox strMessage, vbExclamation, "Invalid operation"
Exit Function
ElseIf Me!cboEndHour = "24" And Me!cboEndMinutes > "0" Then
strMessage = "Finishing time cannot be after 24:00 hours"
MsgBox strMessage, vbExclamation, "Invalid operation"
Exit Function
End If

' ensure time range does not overlap with another record for current
employee
strCriteria = "((" & USDateTime(dtmStartTime) & " >= StartTime " & _
"And " & USDateTime(dtmStartTime) & " < EndTime) " & _
"Or (" & USDateTime(dtmEndTime) & " <= EndTime " & _
"And " & USDateTime(dtmEndTime) & " > StartTime) " & _
"Or (" & USDateTime(dtmStartTime) & " <= StartTime " & _
"And " & USDateTime(dtmEndTime) & " >= EndTime)) " & _
" And EmployeeID =" & Me!cboEmployee
Debug.Print strCriteria
If Not IsNull(DLookup("TimeRecordID", "Times", strCriteria)) Then
strMessage = "Times conflict with times previously entered for "
& _
"this employee on " & Format(dtmStartTime, "dddd dd mmmm
yyyy")
MsgBox strMessage, vbExclamation, "Invalid operation"
Exit Function
End If

End If

ValidateTimes = True

End Function

The function calls the following USDateTime function to format the date/time
values as date/time literals in US format. In Access date/time literals must
be in this or an otherwise internationally unambiguous format:

Public Function USDateTime(dtmDateTime As Date) As String

' returns date/time as string in US date format

On Error GoTo Err_Handler

USDateTime = "#" & Format(dtmDateTime, "mm/dd/yyyy hh:nn:ss") & "#"

Exit_Here:
Exit Function

Err_Handler:
MsgBox Err.Description & " (" & Err.Number & ")", vbExclamation, "Error"
Resume Exit_Here

End Function

Ken Sheridan
Stafford, England

 

[Jamie Collins]

The above is just an example, but does give yo the idea.

If the OP wants to implement data validation at the Jet engine level,
and I suggest they do, then AFAIK a table-level CHECK constraint is
required. I gave a full example here:

http://groups.google.com/group/microsoft.public.access.forms/msg/04c3f233ba3336cc

(look for CONSTRAINT earnings_history__no_overlapping_periods)

Alternatively, the OP could use procs/'parameter queries' to control
INSERT/UPDATE operations with appropriate permissions granted to the
procs (and revoked from the base tables) but certain regulars don't
consider this to be a genuinely engine-level solution

Jamie.

--

 

[Sunny]

Thanks Ken, Albert, Jamie, this was super help !