Ok, so this database is close to what I need so perhaps I can use it as a base.
I have been tweaking some of the code, but I can't get something to work.
The following code checks if there is an overlap between a new holiday that is being entered and an existing one. If there is an overlap, the code assigns a value to the variable HolidayCheck which updates into HolidayID. This Holiday ID contains the data of the holiday that the new reservation overlaps either at the front or at the back.
Code:
Public Function HolidayCheck(vStart As Date, vEnd As Date, vEmployeeID As Long, vHolidayID As Long) As Long
'Checks if new holiday period overlaps any existing holiday period, return 0 if no or return overlapped holiday period ID if yes
'Entry (vStart) = Start date of holiday period
' (vEnd) = End date of holiday period
' (vEmployeeID) = ID of employee to check
' (vHolidayID) = ID of record being checked (excluded from checks so it does not show it overlaps itself)
'Exit HolidayCheck = ID of holiday period record if new period overlaps or 0 if no overlap
Dim rst As Recordset
HolidayCheck = 0 'return 0 if no match found
Set rst = CurrentDb.OpenRecordset("SELECT * FROM tblHolidayDates WHERE EmployeeID = " & vEmployeeID & " AND HolidayID <> " & vHolidayID) 'make list of holiday dates except the one being changed
Do Until rst.EOF
If vStart <= rst!EndDate And vEnd >= rst!StartDate Then 'if new start <= existing end and new end >= existing start then
HolidayCheck = rst!HolidayID 'new holiday period overlaps this holiday so return ID of this holiday record
Exit Do 'no need to check any more
End If
rst.MoveNext 'next record
Loop
rst.Close
Set rst = Nothing
End Function
However, what I need it to do is to get it to update either vStart or vEnd to match the holiday period that it overlaps. In other words the code needs to find out if the overlap is at the beginning or at the end of the holiday period and then it needs to change vStart to a later date or vEnd to an earlier date so that there are no conflicts. I guess it needs to through the table again after doing it once to check for other holidays too.
How do do this? I mean, I have a table (tblHolidayDates), I have the value of the variable "HolidayID" and that row has one "StartDate" and one "EndDate". I need to pull those two dates, compare them to my vStart and vEnd and update them if necessary. Is my logic correct? How do I do this?
All I am trying to do is to allow for holidays to end on a date and another holiday to start the same date that the first one ends. Or conversely, have a holiday that ends the same date that another one starts.