Find Gated Dates In Table

[CJBIRKIN]

Hi

This is a little complicated. I have had to create a table of Rules that are translated via code into dates and times. I.e staffid, first monday after start of month , 09:00 , 11:00, location 2,rule#.

would create a record containing the staffid , date of the first monday in the month and the times and location 2 and the rule number.

What i am doing is creating a table of these date/times to check that dates and times do not OVERLAP for either the same location or another location.

In this example this is incorrect as the start and end time of the second record "gates" the start time of the first record. So i can't use keys to prevent this from happening.

staffid----SDate-----------Stime-------Etime--------Location------Rule#
----1----23/08/2003------08:00------17:00--------location2-------1
----1----23/08/2003------07:00------09:00--------location1-------2

This is ok

staffid----SDate-----------Stime-------Etime--------Location------Rule#
----1----23/08/2003------08:00------17:00--------location2-------1
----1----23/08/2003------17:50------18:00--------location1-------2


All(!) i need to do is tell the user which rules are incorrect

I am quite happy to use VBA to solve this if you've got any ideas?


Cheers Chris

 

[WayneRyan]

Chris,

Can't do it with a query ...

Dim dbs As DAO.Database
Dim rst1 As DAO.Recordset
Dim rst2 As DAO.Recordset
Dim sql As String

Set dbs = CurrentDb
sql = "Select * " & _
      "From   Rules " & _
      "Order By staffid"

Set rst1 = dbs.OpenRecordset(sql)
While Not rst1.EOF and Not rst1.BOF
  sql = "Select * " & _
        "From   Rules " & _
        "Where  (staffid = " & rst1!staffid & " And " & _
        "        SDate = #" & rst1!SDate & "# And " & _
        "        Stime >= " & rst1!Stime And " & _
        "        Stime <= " & rst1!Etime) Or
        "       (staffid = " & rst1!staffid & " And " & _
        "        SDate = #" & rst1!SDate & "# And " & _
        "        Etime >= " & rst1!Stime And " & _
        "        Etime <= " & rst1!Etime) And " & _
        "       (staffid <> " & rst1!staffid & " And " & _
        "        SDate <> #" & rst1!SDate & "# And " & _
        "        Stime <> " & rst1!Stime And " & _
        "        Etime <= " & rst1!Etime)
   Set rst2 = dbs.OpenRecordset(sql)
   If Not rst2.EOF And Not rst2.BOF Then
      MsgBox("Message, Insert to table, Flag record ...")
   End If
   rst1.MoveNext
   wend

Don't have Access with me, but it should be close.

Wayne

 

[MattS]

Chris,

This is an amended version of something I use to check overlapping dates. I created a test table (tblRules) and it seems to work.

Wasn't sure if you neede to check overlapping times for a location or a member of staff, so this checks both.

Hope it helps,

Matt.

Public Sub sCheckRules(lngStaffID As Long, strLocation As String, datSDate As Date, datSTime As Date, datETime As Date)

Dim rstTest As Recordset
Set rstTest = CurrentDb.OpenRecordset("tblRules")
    With rstTest
    .MoveFirst
    
    Do Until .EOF
    If ![StaffID] = lngStaffID And ![SDate] = datSDate And ((datSTime >= ![STime] And datSTime <= ![ETime]) Or (datETime >= ![STime] And datETime <= ![ETime])) Then
    MsgBox "Employee already doing something then": GoTo LeaveNow
    End If
    .MoveNext
    Loop
    
    .MoveFirst
    
    Do Until .EOF
    If ![Location] = strLocation And ![SDate] = datSDate And ((datSTime >= ![STime] And datSTime <= ![ETime]) Or (datETime >= ![STime] And datETime <= ![ETime])) Then
    MsgBox "Somebody already at this location then": GoTo LeaveNow
    End If
    .MoveNext
    Loop
    
    MsgBox "All OK!!"
LeaveNow:
    End With
    rstTest.Close
End Sub

 

[CJBIRKIN]

Hi

Thanks guys i'll have a look at this today.


Chris