[AccessD] Appointment collisions

Lets try that again.  I got a couple of other functions in the first email


For anyone who has never seen the code, Lembit donated the following code
which I documented and placed into a function which checks for appointment
collisions.  I just finished building a set of two queries and a report to
display any collisions between times entered into my billing programs.  I
work on site a lot and if I don't have my laptop I keep an email open which
I then email home to myself with my hours and a brief summary of what I am
doing.  In any case I end up copying that into my billing program every few
days.  I have always "manually" checked my hours to make sure I didn't enter
times in wrong, but a simple report to tell me if I am entering overlapping
times is a must.  There is nothing more embarrassing that billing the
customer twice for the same time!

The following is the function.

'Comments  :    THANKS TO LEMBIT SOOBIK
'there is a 'simple' equation which defines a conflict in appointments:
'let's name the starting point of an existing appointment Se, endpoint Ee
'and for the new appointment to be tested the starting point St, endpoint Et
'now you can simply check
'
'If St < Ee AND Et > Se
'
'then you have a conflict.
'you can easily visualize that by following drawing
'
'_________Se______Ee______
'1_St__Et
'2_St_________Et
'3_St__________________Et
'4___________St_Et
'5___________St________Et
'6_____________________St__Et
'
'As you can see, only cases 2, 3, 4, 5 have a conflict. case 1 does not have
a conflict because it ends before the existing one starts (Et < Se) and 6
does not have a conflict since it starts after the existing ends (St > Ee.
'
'Parameters:
'Sets      :
'Returns   :
'Created by: Colby Consulting
'Created   : 6/25/99 11:43:56 AM
Function ccApptCollision(IDNew As Long, dtmSTNew As Date, dtmETNew As Date,
_
                        IDExist As Long, dtmSTExist As Date, dtmETExist As
Date) As Boolean
On Error GoTo Err_ccApptCollision
#If boolELE Then
    Call gProcStack.EnterProc("ccApptCollision", "Module1")
#End If

    'CHECK IF WE ARE COMPARING A RECORD TO ITSELF
    If IDNew = IDExist Then
        ccApptCollision = False
        Exit Function
    End If

    'CHECK FOR COLLISIONS
    If (dtmSTNew < dtmETExist) And (dtmETNew > dtmSTExist) Then
        ccApptCollision = True
    Else
        ccApptCollision = False
    End If

Exit_ccApptCollision:
#If boolELE Then
    Call gProcStack.ExitProc("ccApptCollision")
#End If
Exit Function

Err_ccApptCollision:
#If boolELE Then
    WriteErrorLog Err
#End If
    Select Case Err
    Case 0      'insert Errors you wish to ignore here
        Resume Next
    Case Else   'All other errors will trap
        Beep
        MsgBox Err.Description, , "Error in function
Module1.ccApptCollision"
    Resume Exit_ccApptCollision
    End Select
    Resume 0    'FOR TROUBLESHOOTING
End Function

**********

In my billing I keep the time and date separate.  I then build a query where
I drop the time table in twice, join on the date, then add the time and date
fields into a single value and pass them into the parameters of the function
above as well as the time record IDs from the two records.  Out pops a true
/ false value that tells me if I have a collision.  You can actually do the
same without any join but the result set can get huge in a hurry so you will
need to limit the date ranges.

Again, thanks to Lembit for the logic.
John W. Colby
www.ColbyConsulting.com