Date Criteria

[Kango]

Help With Date Criteria!!!!!

Hi People,

I have a simple date problem, and was hoping for some help please. Basically I have a table with a flight schedule . The flight schedule lists flights operating for a season normally 6 months. The schedule has a StartFlightDate field and an EndFlightDate field. The FlightSchedule table also has 7 fields for operating days of the week – Mon ; Tue; Wed; Thu; Fri; Sat; Sun, these are check boxes and indicate the days of the week in which the flight operates ( as some flights only operate on certain days of the week). The table look’s something like:

FLIGHT NO STARTDATE ENDDATE Mon Tue Wed Thu Fri Sat Sun

KT869 28/10/2003 31/03/2004 ü ü ü
KT868 28/10/2003 31/03/2004 ü ü ü
KT867 28/10/2003 31/03/2004 ü ü ü


I need to create a query that will list all flights for today’s ( or any given date) that fall between the start and end date and match the day so for example if I type 8/03/2004 (which happens to be a Monday) KT869 and KT868 would be displayed , as the date is within the Start and End date range ,and it is a Monday. What criteria would I need for the query to get this result?

Would be grate for any advice.

Many Thanks


Kango

 

[AN60]

Can you use the following and when you want one date type the same date in for the beginning & end?

Between[beginning date] And [ending date]

 

[Kango]

Thanks For That

What about implementing the days of week?.

Any Ideas

Thanks
Milan

 

[raskew]

Say you had tblFlightSched, which looked like this

FlightNo FirstDay	   LastDay	    FlyDays
FL100	28-Oct-03	31-Mar-04	12345
FL101	28-Oct-03	31-Mar-04	23456
FL102	28-Oct-03	31-Mar-04	34567
FL200	28-Oct-03	31-Mar-04	1357
FL201	28-Oct-03	31-Mar-04	246
FL202	28-Oct-03	31-Mar-04	357
FL300	01-Jan-04	30-Jun-04	76543
FL301	01-Jan-04	30-Jun-04	65432
FL302	01-Jan-04	30-Jun-04	54321
FL400	01-Jan-04	30-Jun-04	7531
FL401	01-Jan-04	30-Jun-04	642
FL402	01-Jan-04	30-Jun-04	531

I used Medium-Date (13-Mar-04) as my weapon of choice
in order to circumvent the regional date problems. In the
US environment I can respond with short-date (mm/dd/yy)
and it works equally well. It may work the same in
your dd/mm/yy setting--you'll have to tell us.
FlyDays are the weekdays (Sun=1 thru Sat=7) that this flight
operates. It makes no difference in what sequence the
weekdays appear in the field.

Then you have a query (qryCheckFlights ? - name makes no
difference) which asks you for your projected flight date.

Run the query, input a date and, with the luck, it should return the
potential flights that operate on the particular weekday of the date
that you've specified.

PARAMETERS [Enter flight date dd-mmm-yy] DateTime;
SELECT
    tblFlightSched.FlightNo
  , tblFlightSched.FirstDay
  , tblFlightSched.LastDay
  , tblFlightSched.FlyDays
  , Format(Weekday([Enter flight date dd-mmm-yy]),"dddd") & ": " & Format(Weekday([Enter flight date dd-mmm-yy])) AS MyFlightDay
FROM
   tblFlightSched
WHERE
   (((tblFlightSched.FirstDay)<=[Enter flight date dd-mmm-yy]) 
AND
   ((tblFlightSched.LastDay)>=[Enter flight date dd-mmm-yy]) 
AND
   ((InStr([Flydays],Weekday([Enter flight date dd-mmm-yy])))>0));

Interested to see how that works for you. Please post back.

Bob

 

[Kango]

Say you had tblFlightSched, which looked like this

FlightNo FirstDay	   LastDay	    FlyDays
FL100	28-Oct-03	31-Mar-04	12345
FL101	28-Oct-03	31-Mar-04	23456
FL102	28-Oct-03	31-Mar-04	34567
FL200	28-Oct-03	31-Mar-04	1357
FL201	28-Oct-03	31-Mar-04	246
FL202	28-Oct-03	31-Mar-04	357
FL300	01-Jan-04	30-Jun-04	76543
FL301	01-Jan-04	30-Jun-04	65432
FL302	01-Jan-04	30-Jun-04	54321
FL400	01-Jan-04	30-Jun-04	7531
FL401	01-Jan-04	30-Jun-04	642
FL402	01-Jan-04	30-Jun-04	531

I used Medium-Date (13-Mar-04) as my weapon of choice
in order to circumvent the regional date problems. In the
US environment I can respond with short-date (mm/dd/yy)
and it works equally well. It may work the same in
your dd/mm/yy setting--you'll have to tell us.
FlyDays are the weekdays (Sun=1 thru Sat=7) that this flight
operates. It makes no difference in what sequence the
weekdays appear in the field.

Then you have a query (qryCheckFlights ? - name makes no
difference) which asks you for your projected flight date.

Run the query, input a date and, with the luck, it should return the
potential flights that operate on the particular weekday of the date
that you've specified.

PARAMETERS [Enter flight date dd-mmm-yy] DateTime;
SELECT
    tblFlightSched.FlightNo
  , tblFlightSched.FirstDay
  , tblFlightSched.LastDay
  , tblFlightSched.FlyDays
  , Format(Weekday([Enter flight date dd-mmm-yy]),"dddd") & ": " & Format(Weekday([Enter flight date dd-mmm-yy])) AS MyFlightDay
FROM
   tblFlightSched
WHERE
   (((tblFlightSched.FirstDay)<=[Enter flight date dd-mmm-yy]) 
AND
   ((tblFlightSched.LastDay)>=[Enter flight date dd-mmm-yy]) 
AND
   ((InStr([Flydays],Weekday([Enter flight date dd-mmm-yy])))>0));

Interested to see how that works for you. Please post back.

Bob

Hi Bob,


Thanks for the help i will give it a go and report back, hopefully your idea will work. Thanks for the help!!!

Kango

 

[Kango]

Hi Bob,

Tested it and it works perfectly!!!!. Thank you very much for helping me , it has saved me lots of time!!!!


Kango