Query based on date

[Costefran]

Another one

I am trying to identify a clash of two records based on dates

As an example to explain lets say I have a car and and Record A shows
That between 20/01/2010 and 22/01/2010 my wife has arranged for a service

Record B shows
I then want to use my car from 19/01/2010 to 21/01/2010 so I run a query or other to check dates

So the reocrds look something like this
A
Start Date End Date Task Description
20/01/2010 22/01/2010 Arranged for service
B
Start Date End Date Task Description
19/01/2010 21/01/2010 Use Car

What would be the easiest way to compare the clash of dates via a query

Thanks again

 

[dhookom]

Assuming you have a primary key field named ID and your table name is "tblNoName" try something like:

SELECT A.ID, B.ID, A.StartDate, B.StartDate, A.EndDate, B.EndDate, A.TaskDescription, B.TaskDescription
FROM tblNoName A , tblNoName B
WHERE A.ID <> B.ID AND A.StartDate<B.EndDate and A.EndDate>B.StartDate

Duane
Hook'D on Access
MS Access MVP

 

[Costefran]

Thanks

I will try this and let you know how I get on