Time interval

[foster]

Greeting all;

I am looking for help for the following scenario
Db with table that have three column
The first column is for Assignment title
The second & third is for Assignment period (start date\time – end date\time)
So what I need is to make sure that the time entered in th next record is not the time between any previous records.

I hope it’s clear, any help would be highly appreciated.

[ADezii]

Greeting all;

I am looking for help for the following scenario
Db with table that have three column
The first column is for Assignment title
The second & third is for Assignment period (start date\time – end date\time)
So what I need is to make sure that the time entered in th next record is not the time between any previous records.

I hope it’s clear, any help would be highly appreciated.

Basic Code logic with Assumptions:

1. Start and End are defined as Date/Time Data Types
2. Start and End are Required Fields
3. End Date must always be greater than the Start Date
4. Table Name is tblAssignments
5. Field Names on Form are txtStart and txtEnd
6. Field Names in Table are Start and End

Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim dteStartDate As Date, dteEndDate As Date, Msg As String
 
Msg = "The Date/Time Ranges you specified overlap the previous Record's Date/Time Range"
 
dteStartDate = DLast("[Start]", "tblAssignment")
dteEndDate = DLast("[End]", "tblAssignment")
 
If IsNull(Me![txtStart]) Or IsNull(Me![txtEnd]) Then
  Cancel = True: Exit Sub
End If
 
 'Make sure there is no overlap with Time Range in previous Record
If (Me![txtStart] > dteEndDate) And (Me![txtEnd] > dteStartDate) Then
  'Date entries OK
Else
  MsgBox Msg, vbCritical, "Invalid Entry"
    Cancel = True
End If
 End Sub