Check for holiday overlap

[xiong]

I'm looking for some ideas here. I have a table of rental reservations that include a arrival date and departure date (dtArrv, dtDepa). I need to compare this date range to another table of holiday date ranges (not single dates). If there is an overlap, I simply need to warn the user that the date range selected includes such and such holiday week/weekend.

I have a table with the holidays listed, but I would need to populate those dates out many years in the future. or strip the year from the date, and make exceptions for leap years. And the Thanksgiving/Easter holidays don't make it any easier either.

Does anyone out there have some ideas for me on which direction to head?


"Only the educated are free.

 

[lameid]

I see two separate issues.

The first is identifying reservations meeting holiday criteria.

Secondly I see you are wanting to populate the Holiday table.

For the first issue if you want to know if there are holidays between the date, I would use a sub form or sub report that uses a query to references the dates on the parent. If it is a sub form, just be sure to requery when the dates change (After update of the date fields and the forms On current events).

The query would look something like:

Select Holiday.HolDate, HolName
From Holiday
Where Holiday.HolDate Forms!frmReservation!dtArrv And Forms!frmReservation!dtDepa

You also mentioned determining if it is a holdiay week. There are two ways to go about determining this. First, include 'Holdiays' that have a Holiday of the week of the Holdiay. Or you could try and expand your criteria... I guess it would depend on if you are looking forward, backward, both or depending on complex criteria like count it as the previous week too if it is on the following Monday. In this scenario you might want to write a function to display the holidays. Again it all depends on what you are after.

For populating the table, I would start with Google but you might consider trying to figure out how to export a list from Outlook. Although, many complain about the completeness of the Outlook list. For all things Outlook and excange slipstick.com is an excellent site. I bet a search there on holidays would turn something up.

 

[xiong]

That's close, but I already have a list of holiday date ranges that we use for this. (dec 23 to dec 27 for 'Christmas', etc.) The main problem is that the reservations include a range as well, with only a start and finish dates. (Dec 18 to the 24th). Since in this example the reservation borders on the holiday range, I need a warning at that point, no table beyond that. The import from Outlook and other sources won't work, as we use entire ranges for the date period.

tblRESV
rv_num rv_arrv rv_depa
1 12/20/07 12/27/07
2 01/01/08 01/08/08
3 12/28/07 01/02/08

tblHOLI
ho_name ho_start ho_end
Xmas 12/23 12/27
NewYear 12/28 01/02
July4 07/02 07/06


Thanksgiving and Easter are other concerns for a later time




"Only the educated are free.

 

[PWise]

Select tblRESV.* 
from 
tblRESV 
inner join  tblHOLI
on
(rv_arrv between ho_start and  ho_end )
or 
(rv_depa between ho_start and  ho_end )
or 
(ho_start between rv_arrv and rv_depa )

(rv_arrv between ho_start and ho_end )
For rv_arrv in Holiday Range

(rv_depa between ho_start and ho_end )
For rv_depa in Holiday Range

(ho_start between rv_arrv and rv_depa )
For Complete Holiday Range between rv_arrv and rv_depa

 

[PHV]

Why not simply this ?
SELECT tblRESV.*
FROM tblRESV
INNER JOIN tblHOLI ON rv_arrv >= ho_start AND rv_depa <= ho_end

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[PWise]

rv_arrv = 12/24 and rv_depa 12/29 will not return anything so i think

 

[PHV]

OOps, sorry for the typo:
SELECT tblRESV.*
FROM tblRESV
INNER JOIN tblHOLI ON rv_depa >= ho_start AND rv_arrv <= ho_end

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886