Validation rule to prevent overlapping time periods

[Stace Cameron]

I have a table:

InactID-AutoNumber
PreceptorID-Number
InactStart-Date/Time
InactEnd-Date/Time

I don't want records that have overlapping time periods for a
PreceptorId. I tried:


[InactStart] < (DLookUp("[InactEnd]","tblInactive","[PreceptorID]=" &
[PreceptorID] & " and [InactID]=" & [InactID]-1))

but found that the DLOOKUP function was not permitted in a table
validation rule.

Any suggestions?

 

[Allen Browne]

You won't be able to use a table validation rule here. Instead, use the
BeforeUpdate event procedure of the *form* where the data is entered. Cancel
the event if there is a clash.

The logic for a clash is:
- Event A starts before Event B ends, AND
- Event B starts before Event A ends.
- Event A and Event B are not the same thing.

You don't need to perform this when editing a record where the time did not
change, and you can't run the test unless you have both time values. So, the
event procedure will be something like this:

Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim strWhere As String
Dim varResult As Variant
Const strcJetDateTime = "\#mm\/dd\/yyyy hh\:nn\:ss\#"

If (Me.[InactStart-Date/Time] = Me.[InactStart-Date/Time].OldValue _
AND Me.[InactEnd-Date/Time] = Me.[InactEnd-Date/Time].OldValue) _
OR IsNull(Me.[InactStart-Date/Time]) _
OR IsNull(Me.[InactEnd-Date/Time]) Then
'Do nothing
Else
strWhere = "(" & Format(Me.[InactStart-Date/Time], strcJetDateTime)
& _
" < [InactEnd-Date/Time]) AND (InactStart-Date/Time] < " & _
Format(Me.[InactEnd-Date/Time], strcJetDateTime) & _
") AND ([InactID-AutoNumber] <> " &
Nz(Me.[InactID-AutoNumber],0) & ")"
varResult = DLookup("[InactID-AutoNumber]", "Table1", strWhere)
If Not IsNull(varResult) Then
Cancel = True
MsgBox "Clash wiht InactID " & varResult
'Me.Undo
End If
End If
End Sub

 

[Jamie Collins]

I have a table:

InactID-AutoNumber
PreceptorID-Number
InactStart-Date/Time
InactEnd-Date/Time

I don't want records that have overlapping time periods for a
PreceptorId. I tried:

[InactStart] < (DLookUp("[InactEnd]","tblInactive","[PreceptorID]=" &
[PreceptorID] & " and [InactID]=" & [InactID]-1))

but found that the DLOOKUP function was not permitted in a table validation rule.

The "table validation rule" is a misnomer (IMO) because it is actually
at the row level. You can use a Jet CHECK constraint (not well exposed
in the Access UI) which is truly table level.

If you periods are whole days then you could use the standard trick of
a Calendar table, then in your CHECK constraint join to this table
using GROUP BY on the calendar date e.g.

CHECK (NOT EXISTS (
SELECT C1.calendar_date
FROM EarningsHistory AS E1,
Calendar AS C1
WHERE C1.calendar_date BETWEEN E1.start_date
AND E1.end_date
GROUP BY E1.employee_id, C1.calendar_date
HAVING COUNT(*) > 1))

For a full demonstration see:

http://groups.google.com/group/microsoft.public.access.tablesdbdesign/msg/0fb58491ff039e1c

Otherwise, use a regular query in a table level CHECK constraint to
test for overlapping periods. The 'textbook' (Snodgrass's -- google
it) query for this (using closed-open representation) in Jet SQL
syntax:

SELECT S1.ssn
FROM SalaryHistory AS S1, Incumbents AS I1
WHERE S1.ssn = I1.ssn
AND IIF(S1.start_date > I1.start_date, S1.start_date, I1.start_date)
< IIF(S1.end_date > I1.end_date, I1.end_date, S1.end_date);

Obviously, you need to write this as a 'self join' for your
constraint. There are some decision to make along the way e.g.
representation (e.g. closed-closed or closed-open or something else),
the candidate keys on your table, ensuring end_date does not occur
before start_date, how to model the period in the current state
(end_date = NULL or end_date = <far future date representing infinity>
or something else), etc. Here's a long post from the google groups
archive which considers such issues:

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

Jamie.

--

 

[Jamie Collins]

You won't be able to use a table validation rule here.
Instead, use the
BeforeUpdate event procedure of the *form* where the data is entered.

I don't think it is right to encourage the OP to move constraints from
the engine to the front end (or rather, each and every front end that
will ever access the data). I think the OP's request for a table
constraint is worthy of consideration. IMO data validation rules
should be defined as close to the data as possible and in this case an
engine level CHECK constraint could work.

Anyhow, isn't a forms solution OT for the 'tables' group <g>?

Jamie.

--

 

[Jamie Collins]

Anyhow, isn't a forms solution OT for the 'tables' group <g>?

Ignore this (why have I strayed outside the 'tables' group <g>?!) I
still think a engine/table level constraint should be preferred over a
'front end' implementation when it comes to data validation.

Jamie.

--

 

[Stace Cameron]

"(not well exposed in the Access UI)" is quite an understatement.

Thanks to your help, I believe I have solved my problem with the
following code:

SQL = "ALTER TABLE tblInactive ADD CONSTRAINT no_overlapping_periods
CHECK (NOT EXISTS (" & _
"SELECT *" & _
" FROM tblInactive A " & _
"where PreceptorID = A.PreceptorID and " & _
"exists( select * from tblInactive B where " & _
"(InactStart BETWEEN A.InactStart AND A.InactEnd) and " & _
"(PreceptorID = A.PreceptorID) and " & _
"(InactID <> A.InactID))));"

CurrentProject.Connection.Execute SQL