Re: Square Peg in a Round Hole

Tech-Archive recommends: Fix windows errors by optimizing your registry

On Thu, 28 Dec 2006 15:43:01 -0800, Andy6
<Andy6@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote:

my question is how do you test the EndDate of record 1 with the StartDate of
record 4 to give, car1 NOT AVAILABLE both times.

You need to compare both the StartDate and the EndDate of each record
with BOTH startdate and enddate of the other record. The overlap can
happen in four ways:

No overlap:
Record1 ____[_____]___________
Record2 ____________[______]__

Second starts during first:
Record1 ____[_____]___________
Record2 _______[_______]______

Second ends during first:
Record1 ____[_____]___________
Record2 _[______]_____________

Second entirely within first:
Record1 ____[_____]__________
Record2 ______[__]___________

A SQL query to detect any overlap would have

(A.StartDate >= B.StartDate AND A.StartDate <= B.EndDate])
OR
(A.EndDate >= B.StartDate AND A.EndDate <= B.EndDate])
OR
(B.StartDate >= A.StartDate AND B.StartDate <= A.EndDate])
OR
(B.EndDate >= A.StartDate AND B.EndDate <= A.EndDate])


John W. Vinson[MVP]



.

Relevant Pages

  • Re: Square Peg in a Round Hole
    ... It's easy to test whether a StartDate an EndDate overlap records in bookings ... Dim dteStart As Date ...
    (microsoft.public.access.queries)
  • Re: SQL Issue
    ... I did some testing and found that it will work if I not only hard code ... But since I need to compare to a Date Variable, ... Dim startdate As Long, enddate As Long, recdate As Long ...
    (microsoft.public.vb.database)
  • Re: operator comparison
    ... >> assuming that, in your table's records, the StartDate is always the same ... >> or before, the EndDate, you'll never find a record matching ... >>> Can you show me how to compare the date I chose between two other ...
    (microsoft.public.access.modulesdaovba)
  • Unmatch return query
    ... I have 2 tables with identical fields ... StartDate DATE ... EndDate DATE ... need to compare StartDate an EndDate with same Emp number, ...
    (microsoft.public.access.queries)
  • Re: Calculate Age in table from Date of Birth and Current Date (MS
    ... 'StartDate' and 'EndDate' cross-references in the with ASK fields to solicit ... you might see where the bookmarks and fields are more easily if you ...
    (microsoft.public.word.tables)