[AccessD] Is one time between two other times

Option Compare Database
Option Explicit
'.
'.=========================================================================
'.Copyright    : CColby Consulting 2001.  All rights reserved.
'.E-mail       : jcolby at ColbyConsulting.com
'.=========================================================================
' DO NOT DELETE THE COMMENTS ABOVE.  All other comments in this module
' may be deleted from production code, but lines above must remain.
'--------------------------------------------------------------------------
'.Description  :
'.Written By   : John W. Colby
'.Date Created : 06/08/2001
'.Rev. History :
'.Comments     : This module provides an "appointment collision" checking
'routine.  Any two records with start and stop times and dates can be fed
into
'this function to check for a collision.
'.-------------------------------------------------------------------------
'.
' ADDITIONAL NOTES:
'
'*+ custom constants declaration
Private Const mcstrModuleName As String = "basCollisionCheck"
Const DebugPrint = True
'*- Custom constants declaration
'*+ custom Variables declaration
'*- custom Variables declaration
'

'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 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:
    On Error Resume Next
Exit Function

Err_ccApptCollision:
    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
'        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
www.ColbyConsulting.com 

Contribute your unused CPU cycles to a good cause:
http://folding.stanford.edu/

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of
paul.hartland at fsmail.net
Sent: Friday, March 18, 2005 7:20 AM
To: accessd
Subject: [AccessD] Is one time between two other times


To all,
I can't seem to figure this out at all, I have a table of which one of the
fields is a start time.  I need to run a query to see if that time is
between 07:00 and 19:00 or 19:00 and 07:00.

Can any help me with this please.

Thanks in advance for your help.

Paul Hartland

-- 

Whatever you Wanadoo:
http://www.wanadoo.co.uk/time/

This email has been checked for most known viruses - find out more at:
http://www.wanadoo.co.uk/help/id/7098.htm
-- 
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com