date period query

[Guest]

Hi
i have a db with employees and their traveling. the traveling table has a
departure and arrival date. All im trying to do is to make a query for
finding all the employees which were on travelling at specific date. My
problem is that the arrival date and departure date appears only the start
and end date and not all the period.
For example, if an employee is out between 25/1/07 and 30/1/07 how i can
find him using as creteria the specific date 3/10/06 date?
Thanks

 

[Guest]

Hi
i have a db with employees and their traveling. the traveling table has a
departure and arrival date. All im trying to do is to make a query for
finding all the employees which were on travelling at specific date. My
problem is that the arrival date and departure date appears only the start
and end date and not all the period.
For example, if an employee is out between 25/1/07 and 30/1/07 how i can
find him using as creteria the specific date ****27/1/07 *** date?
Thanks

 

[Guest]

Try using this as criteria in the query for each field

Under Start Date (departure)

= [Please Enter a Date]

Under End Date (arrival)
<= [Please Enter a Date]

So, as SQL
Select * From TableName Where [Start Date] >= [Please Enter a Date] And [End
Date] <= [Please Enter a Date]

 

[John Spencer]

I always have a problem with this as I often reverse the comparision.

I think you want to check for the
Departure Date is on or before the target date and
the Return Date is on or after the target date

Departure <= [Target Date] and Return >= [Target Date]

So if Target Date is today March 5, 2007
and I departed on March 3 and returned on March 9
then I was traveling on March 5

Target Date March 5; Departed March 3; returned March 4
then I was not traveling on March 5

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

Try using this as criteria in the query for each field

Under Start Date (departure)

= [Please Enter a Date]

Under End Date (arrival)
<= [Please Enter a Date]

So, as SQL
Select * From TableName Where [Start Date] >= [Please Enter a Date] And
[End
Date] <= [Please Enter a Date]


--
Good Luck
BS"D

Hi
i have a db with employees and their traveling. the traveling table has a
departure and arrival date. All im trying to do is to make a query for
finding all the employees which were on travelling at specific date. My
problem is that the arrival date and departure date appears only the
start
and end date and not all the period.
For example, if an employee is out between 25/1/07 and 30/1/07 how i can
find him using as creteria the specific date 3/10/06 date?
Thanks

 

[Guest]

Hi John,

You are right. thanks.

--
Good Luck
BS"D

I always have a problem with this as I often reverse the comparision.

I think you want to check for the
Departure Date is on or before the target date and
the Return Date is on or after the target date

Departure <= [Target Date] and Return >= [Target Date]

So if Target Date is today March 5, 2007
and I departed on March 3 and returned on March 9
then I was traveling on March 5

Target Date March 5; Departed March 3; returned March 4
then I was not traveling on March 5

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

Try using this as criteria in the query for each field

Under Start Date (departure)

= [Please Enter a Date]

Under End Date (arrival)
<= [Please Enter a Date]

So, as SQL
Select * From TableName Where [Start Date] >= [Please Enter a Date] And
[End
Date] <= [Please Enter a Date]


--
Good Luck
BS"D

Hi
i have a db with employees and their traveling. the traveling table has a
departure and arrival date. All im trying to do is to make a query for
finding all the employees which were on travelling at specific date. My
problem is that the arrival date and departure date appears only the
start
and end date and not all the period.
For example, if an employee is out between 25/1/07 and 30/1/07 how i can
find him using as creteria the specific date 3/10/06 date?
Thanks

 

[Jamie Collins]

I think you want to check for the
Departure Date is on or before the target date and
the Return Date is on or after the target date

Departure <= [Target Date] and Return >= [Target Date]

So if Target Date is today March 5, 2007
and I departed on March 3 and returned on March 9
then I was traveling on March 5

Target Date March 5; Departed March 3; returned March 4
then I was not traveling on March 5

More simply:

WHERE Target_Date BETWEEN Departure_Date AND Return_Date

The question is one of whether a instant falls with within a period
and the start and end dates of that period will have time elements
e.g.

CREATE TABLE Travelling (
departure_date DATETIME NOT NULL,
return_date DATETIME NOT NULL
)
;
CREATE PROCEDURE GetTravelDates (
target_date DATETIME = NOW()
)
AS
SELECT departure_date, return_date
FROM Travelling
WHERE target_date BETWEEN departure_date AND return_date
;
INSERT INTO Travelling (departure_date, return_date)
VALUES (#2007-03-03 07:00:00#, #2007-03-09 18:00:00#)
;
EXECUTE GetTravelDates #2007-03-05 12:00:00#
;

The above execute returns the row.

Jamie

--