Page 1 of 2 12 LastLast
Results 1 to 15 of 21
  1. #1
    Silver Lounger
    Join Date
    Jun 2002
    Location
    Cheadle, Staffordshire
    Posts
    2,177
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Check Between Dates (A2k)

    I'm asking my database to check if a staff member is available between two dates with:

    <pre>Private Sub cmbFitter_AfterUpdate()
    Dim StaffCheck As Integer

    StaffCheck = DCount("*", "tblAttPeriod", "UnitID=" & Me.cmbFitter.Column(6) & _
    " And " & BuildCriteria("FromDate", dbDate, "<=" & Me.txtIn) & _
    " And " & BuildCriteria("ThruDate", dbDate, ">=" & Me.txtOut))
    'If a duplicate has been encounterred, warn the user:
    If StaffCheck > 0 Then
    ' Warn the user
    MsgBox "This Staff Member Is Not Available", vbCritical
    ' Cancel the update
    Exit Sub
    End If
    End Sub</pre>


    I've tried both Before update and After update, StaffCheck always returns 0
    Am I missing something?

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Check Between Dates (A2k)

    Try

    StaffCheck = DCount("*", "tblAttPeriod", "UnitID=" & Me.cmbFitter.Column(6) & _
    " And " & BuildCriteria("FromDate", dbDate, "<=" & Me.txtOut) & _
    " And " & BuildCriteria("ThruDate", dbDate, ">=" & Me.txtIn))

    You should use the Before Update event and set its Cancel argument to True if the fitter is not available. After Update cannot be canceled.

  3. #3
    Silver Lounger
    Join Date
    Jun 2002
    Location
    Cheadle, Staffordshire
    Posts
    2,177
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Check Between Dates (A2k)

    Hans, that works fine.
    But, how did swapping the dates make a difference?

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Check Between Dates (A2k)

    For two date/time intervals to overlap, the start of the first one must be on or before the end of the second one, and the end of the first one must be on or after the start of the first one. See the picture below - in #1, the conditions are satisfied, but in #2, they aren't: the start of the first interval is before the end of the second one, but the end of the first one is before the start of the second one.

  5. #5
    Silver Lounger
    Join Date
    Jun 2002
    Location
    Cheadle, Staffordshire
    Posts
    2,177
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Check Between Dates (A2k)

    Thankyou Hans
    This is much clearer now.

  6. #6
    Silver Lounger
    Join Date
    Jun 2002
    Location
    Cheadle, Staffordshire
    Posts
    2,177
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Check Between Dates (A2k)

    Hans

    I need a tweak here.
    Lets say we have the two date ranges:

    (Range 1) 01/08/2006 to 10/08/2006
    (Range 2) 09/08/2006 to 18/08/2006

    Range 2 overlaps by only one day and returns StaffCheck as 1, where in actual fact the staff member (Whose holidays are range 2) has been back for 9 days and is available.

    Any suggestions ?

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Check Between Dates (A2k)

    StaffCheck only tells you whether the staff member is available during the entire date range. It is possible to change it so that it tells you whether the staff member is available during any part of the date range. But do you really want that? Suppose the staff member is available on only one day of a 10-day date range - is that still useful to you?

  8. #8
    Silver Lounger
    Join Date
    Jun 2002
    Location
    Cheadle, Staffordshire
    Posts
    2,177
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Check Between Dates (A2k)

    Hans

    Yes, it would have to be.
    The user can decide whether to accept this option even if he is only back for one day.
    The whole repair operation doesn't involve just this member of staff.

    Thanks

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Check Between Dates (A2k)

    Since the new requirement is not symmetric with respect to the new date ranges, I need to verify their meaning, just to make sure.
    What do txtIn and txtOut signify? And what do FromDate and ThruDate signify?

  10. #10
    Silver Lounger
    Join Date
    Jun 2002
    Location
    Cheadle, Staffordshire
    Posts
    2,177
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Check Between Dates (A2k)

    Hans

    txtIn = Vehicle book in date
    txtOut = Vehicle book out date
    FromDate = Staff Members first date of his holiday
    ThruDate = Staff Members last day of his holiday

    Hope this helps !

  11. #11
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Check Between Dates (A2k)

    Dave,

    Try the following. Note that the check has changed to If StaffCheck = 0 Then. StaffCheck now tests whether the staff member is available at all, and returns 0 if not.

    Private Sub cmbFitter_BeforeUpdate(Cancel As Integer)
    Dim StaffCheck As Integer

    StaffCheck = DCount("*", "tblAttPeriod", "UnitID=" & Me.cmbFitter.Column(6) & _
    " And (" & BuildCriteria("FromDate", dbDate, "<" & Me.txtIn) & _
    " Or " & BuildCriteria("ThruDate", dbDate, ">" & Me.txtOut) & ")")
    If StaffCheck = 0 Then
    ' Warn the user
    MsgBox "This Staff Member Is Not Available", vbCritical
    ' Cancel the update
    Cancel = True
    End If
    End Sub

  12. #12
    Silver Lounger
    Join Date
    Jun 2002
    Location
    Cheadle, Staffordshire
    Posts
    2,177
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Check Between Dates (A2k)

    Hans

    I set a range of (txtIn)01/08/2006 to (txtOut)10/08/2006 and gave one staff member a range of (FromDate)05/08/2006 to (ThruDate)05/08/2006

    StaffCheck returned 6 and didn't prompt the user that he was not available?

  13. #13
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Check Between Dates (A2k)

    Let's see if we can get this straight. Please correct me if I interpret things incorrectly.

    txtIn = 01/08/2006 means the vehicle was booked in on the 1st of August.
    txtOut = 10/08/2006 means the vehicle was booked out on the 10th of August.

    FromDate = 05/08/2006 and ThruDate = 05/08/2006 means that the staff member has only a single day off - the 5th of August. So this staff member is available on all other days.

    You wanted to know if the staff member was available on *any* of the days between txtIn and txtOut, and this is true.

  14. #14
    Silver Lounger
    Join Date
    Jun 2002
    Location
    Cheadle, Staffordshire
    Posts
    2,177
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Check Between Dates (A2k)

    You know, I was scepticle when I was asked to look into this.
    I think I can see where you're heading.


    Given the date range, yes the member of staff is available for 9 of the days.
    The user would need to be prompted of this so he can make the decision as to whether he should give this job to him or someone else.
    So with the above date range.

    [AirCode]
    If StaffCheck = ? Then
    MsgBox " Warning !, This staff member is not available between the dates of " & DateFrom & " And " & DateThru & _
    "Do you want to select another staff member ?
    End If

    Basically giving the user control instaed of just bombing him out.

  15. #15
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Check Between Dates (A2k)

    You could do something like this:

    Private Sub cmbFitter_BeforeUpdate(Cancel As Integer)
    Dim StaffCheck As Integer

    StaffCheck = DCount("*", "tblAttPeriod", "UnitID=" & Me.cmbFitter.Column(6) & _
    " And (" & BuildCriteria("FromDate", dbDate, "<" & Me.txtIn) & _
    " Or " & BuildCriteria("ThruDate", dbDate, ">" & Me.txtOut) & ")")
    If StaffCheck = 0 Then
    ' Warn the user
    MsgBox "This staff member is not available during the requested period.", vbCritical
    ' Cancel the update
    Cancel = True
    Else
    StaffCheck = DCount("*", "tblAttPeriod", "UnitID=" & Me.cmbFitter.Column(6) & _
    " And " & BuildCriteria("FromDate", dbDate, "<=" & Me.txtOut) & _
    " And " & BuildCriteria("ThruDate", dbDate, ">=" & Me.txtIn))
    If StaffCheck > 0 Then
    If MsgBox("This staff member is available on only some of the days." & vbCrLf & _
    "Select this staff member anyway?", vbQuestion + vbYesNo) = vbNo Then
    Cancel = True
    End If
    End If
    End If
    End Sub

    If you want to display the actual dates on which the staff member is not available, it would become more complicated - you'd have to loop through the records of tblAttPeriod to assemble a list of dates, or pop up a form.

Page 1 of 2 12 LastLast

Posting Permissions

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