Results 1 to 2 of 2
  1. #1
    Join Date
    Nov 2003
    Posts
    18

    Unanswered: -

    -
    Last edited by TheGame7; 11-26-03 at 09:11.

  2. #2
    Join Date
    Feb 2003
    Location
    Auckland, NZ
    Posts
    150
    I would use VBA in this instance

    Before a booking is finalised(Maybe a button being clicked) check to see if the start date and end date of the current booking is Between the start and end date of another booking for that same pitch

    Below is an example

    Code:
    Function CheckBooking (PitchID as Long, StartDate as Date, Endate as Date) As Boolean
    Dim Db as Database
    Dim Rst as RecordSet 
    Dim SqlStr as String
    
    SqlStr = "SELECT * FROM tblBookings " & _
                 "WHERE pitch_id=" & PitchID & " AND " & _
                 "start_date <= #" & _
                 Format(StartDate, "dd-mmm-yyyy hh:nn") & "# AND " & _
                 "end_date>= #" & _
                 Format(StartDate, "dd-mmm-yyyy hh:nn") & "# & _
                 "OR pitch_id=" & PitchID & " AND start_date <= #" & _
                 Format(EndDate, "dd-mmm-yyyy hh:nn") & "# " & _
                 "AND end_date>= #" & _
                 Format(EndDate, "dd-mmm-yyyy hh:nn") & "#;"
    
    set Db = CurrentDb
    Set Rst = Db.OpenRecordSet(SqlStr)
    
    with Rst
    
    If .EOF and .BOF
    'If no Records Found then no clash - return True
    CheckBooking  = True
    
    Else
    'If there are records there is a clash
    CheckBooking = False
    End If
    
    .Close
    End With
    
    End Function
    In the function you just give it the parameters and it will return True if there are NO clashes else it will return False

    I haven't checked the syntax so beware
    Last edited by machado; 11-16-03 at 14:54.
    JJ Kennedy
    Double J IT Solutions
    www.doublejit.co.nz

    VB 6, VB.NET, ASP.NET, MS SQL Server, MySQL, MS Access

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •