[AccessD] Did you know? Or more correctly - I never knew...

I am using Lembit's mighty fine appoint collision function to determine
whether records belong in a result set.  The date parameters are typed as
dates.  In the current application I am getting "invalid data type" passed
in (nulls).  So I created a base query that narrows down to just a dataset
where there are always dates in the fields being passed in.  I then built a
dataset on top of that feeding the dates into the Appt Collision function.
STILL getting errors.
 
It turns out that even though the base query pulls out only the records with
dates, it apparently hands ALL the records up to the query built on top of
the base query and since some of those records have nulls in the dates, the
appointment function complains about nulls passed in to the date fields.
 
So... why is it doing this, and is there a way to force the base query to
only pass up the filtered result set such that only valid dates are pulled?
 
'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 Variant, dtmETNew As
Variant, _
                        IDExist As Long, dtmSTExist As Variant, dtmETExist
As Variant) As Boolean
On Error GoTo Err_ccApptCollision
#If boolELE Then
    Call gProcStack.EnterProc("ccApptCollision", "Module1")
#End If
 
    Debug.Print dtmSTNew
'    If IsNull(dtmSTNew) Or IsNull(dtmSTExist) Then
'        ccApptCollision = False
'        Exit Function
'    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
    On Error Resume Next
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
        LogError mcstrModuleName, err.Number, err.Description, "Error in
function Module1.ccApptCollision"
    Resume Exit_ccApptCollision
    End Select
    Resume 0    'FOR TROUBLESHOOTING
End Function

 
John W. Colby
Colby Consulting
www.ColbyConsulting.com