dates between dates

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a table that holds details about dates of supervision.

The table which includes the field’s supStart and SupEnd. Both are date
fields formatted to shortdate.

Eg.

Name SupStart SupEnd
------------------------------------
J bloggs 1/1/05 30/4/05
A bloggs 1/2/05 3/5/05
C Bloggs 1/3/05 12/4/05

What I need to do is query the data and pull out information of all people
who have been supervised between two user specified dates.

Eg All people supervised between 15/1/05 to 15/4/05
----------------- “ -------------- 5/4/05 to 1/5/05

Can anyone point me in the right direction?

Paul
 
(FromDate >= SupStart And FromDate <= SupEnd) Or _
ToDate >= SupStart and ToDate <= SupEnd
 
Two events overlap if:
A starts before B ends, AND
B starts before A ends.

The WHERE clause of your query will therefore contain:
WHERE (SupStart < #4/15/2005#)
AND (#1/15/2005# < SupEnd)

Since your date examples are for a d/m/y country, you might like to check:
International Date Formats in Access
at:
http://allenbrowne.com/ser-36.html
 
Thanks to you both.

I will give it a go.

Paul

Allen Browne said:
Two events overlap if:
A starts before B ends, AND
B starts before A ends.

The WHERE clause of your query will therefore contain:
WHERE (SupStart < #4/15/2005#)
AND (#1/15/2005# < SupEnd)

Since your date examples are for a d/m/y country, you might like to check:
International Date Formats in Access
at:
http://allenbrowne.com/ser-36.html
Click to expand...
 
Back
Top