Property renting calendar

[mafhobb]

I managed to make it work by adding "ptrsafe" after "declare"

I am now checking how it works.

mafhobb

 

[mafhobb]

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.

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?

mafhobb

 

[theDBguy]

I figured it out. I just added "prtafe" on the "shell execute module and I got it working.
mafhobb

Good job!

 

[mafhobb]

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.

This would basically do 90% of what I need.

mafhobb