Validating Date Ranges

[Terri]

I have an Accrual record with a startdate and enddate. Say StartDate =
10/1/2005 and EndDate = 10/31/2005

A user enters a second record StartDate = 10/15/2005 and EndDate =
10/17/2005. I want to validate the second record on the BeforeUpdate event
and prevent the user from entering this record. Since the first record is
10/1/2005 - 10/31/2005 I consider this date range closed and want to prevent
additional records that are any where within the date range of the first
record.

How can I build a query with the following parameters, StartDate =
10/15/2005 and EndDate = 10/17/2005, that will return the first record.

((dbo_Accruals.AccrualStartDate) between #10/15/2005# AND #10/17/2005#)) OR
((dbo_Accruals.AccrualEndDate) between #10/15/2005# AND #10/17/2005#))
obviously won't work.

 

[Van T. Dinh]

Try:
( (dbo_Accruals.AccrualStartDate <= #10/15/2005#) AND
(dbo_Accruals.AccrualEndDate >= #10/17/2005#) ) OR
(dbo_Accruals.AccrualStartDate between #10/15/2005# AND #10/17/2005#) OR
(dbo_Accruals.AccrualEndDate between #10/15/2005# AND #10/17/2005#)

IIRC, there was a post on how to get the overlapping Records with very
efficient logic but I can't think of it at the moment.