Page 1 of 2 12 LastLast
Results 1 to 15 of 17
  1. #1
    Join Date
    Aug 2009
    Posts
    61

    Unanswered: Do not allow employees to select a holiday date for start of vacation

    Hi There,

    I have a vacation tracking databse set up where employees select the start date and time and finish date and time for their vacation. I have code running to count the number of hours that they are using. Unfortunately the code does not seem to work if the employee selects that start date that ios actually a holiday date. I need one of two thing:

    1. Modify this code so that if the start date is on a holiday date, no time is used.
    2. Create a validation rule in the table so that a date entered in the startdate field cannot be a date contained in the holidays table.


    Here is the code that I have runnign at the moment.

    Code:
    Option Compare Database
    
    Public Function NetWorkhours(Start As Date, Finish As Date, Spellout As Boolean) As Variant
    
    Dim intGrossDays As Integer
    Dim intGrossMins As Single
    Dim dteCurrDate As Date
    Dim i As Integer
    Dim WorkDayStart As Date
    Dim WorkDayend As Date
    Dim nonWorkDays As Integer
    Dim StartDayMins As Single
    Dim EndDayMins As Single
    Dim NetworkMins As Integer
    NetworkMins = 0
    nonWorkDays = 0
    'Calculate work day hours on 1st and last day
    
    WorkDayStart = DateValue(Finish) + TimeValue("8:30:00")
    WorkDayend = DateValue(Start) + TimeValue("16:00:00")
    StartDayMins = DateDiff("n", Start, WorkDayend)
    EndDayMins = DateDiff("n", WorkDayStart, Finish)
    'adjust for time entries outside of business hours
    
    
    'Calculate total hours and days between start and end times
    
    intGrossDays = DateDiff("d", (Start), (Finish))
    intGrossMins = DateDiff("n", (Start), (Finish))
    
    'count number of weekend days and holidays (from a table called "Holidays" that lists them)
    
    For i = 0 To intGrossDays
        dteCurrDate = Start + i
        If Weekday(dteCurrDate, vbSaturday) < 3 Then
            nonWorkDays = nonWorkDays + 1
        Else
            If Not IsNull(DLookup("[HolDate]", "Holidays", "[HolDate] = #" & Int(dteCurrDate) & "#")) Then
            nonWorkDays = nonWorkDays + 1
            End If
        End If
    Next i
    'Calculate number of work hours
    
    Select Case intGrossDays
        Case 0
            'start and end time on same day
            NetworkMins = (intGrossMins - ((nonWorkDays) * 1440))
        Case 1
            'start and end time on consecutive days
            NetworkMins = StartDayMins + EndDayMins
        Case Is > 1
            'start and end time on non consecutive days
            NetworkMins = (((intGrossDays - 1) - nonWorkDays) * 480) + (StartDayMins + EndDayMins)
        End Select
        If Spellout = True Then
            NetWorkhours = MinsToTime(NetworkMins) ' hours and mins
        Else
            NetWorkhours = NetworkMins ' minutes only
        End If
        
        
    End Function

  2. #2
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    1) Have you set a breakpoint and stepped through the code? It appears to account for holidays, and looks like it would include the first date in that process.

    2) I don't see a validation rule being able to handle this. If you have 2010, a data macro might do it, but I haven't used them enough to be sure. I'd have code in the form's before update event to validate the date before the record got saved.
    Paul

  3. #3
    Join Date
    Aug 2009
    Posts
    61
    I tried to step through the best I could (still novice) and I cannot see why it it is causing a problem.

    For example, I have the 8/1/2011 as a holiday in my table. I have selected a vacation range of 8/1/2011 7:30 am until 8/2/2011 4:00 PM. This returns 16 hours, but it should return 8.

    If I select the start and stop date/time on 8/1/2011, it gives me -15:30.

  4. #4
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    Could be the time value, though it looks like somebody tried to account for it. Try changing this in the holiday test:

    Int(dteCurrDate)

    to this:

    DateValue(dteCurrDate)

    If that doesn't work, can you post the db or a representative sample here?
    Paul

  5. #5
    Join Date
    Aug 2009
    Posts
    61
    okay so tried that and it did not work. I ahve attached a stripped down version of the DB. let me know if you need more.
    Attached Files Attached Files

  6. #6
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    See if this works; appears to with the limited data in the sample:

    Code:
    Public Function NetWorkhours(Start As Date, Finish As Date, Spellout As Boolean) As Variant
    
      Dim intGrossDays            As Integer
      Dim intGrossMins            As Single
      Dim dteCurrDate             As Date
      Dim i                       As Integer
      Dim WorkDayStart            As Date
      Dim WorkDayend              As Date
      Dim nonWorkDays             As Integer
      Dim StartDayMins            As Single
      Dim EndDayMins              As Single
      Dim NetworkMins             As Integer
      NetworkMins = 0
      nonWorkDays = 0
      'Calculate work day hours on 1st and last day
    
      WorkDayStart = DateValue(Finish) + TimeValue("8:30:00")
      WorkDayend = DateValue(Start) + TimeValue("16:00:00")
      StartDayMins = DateDiff("n", Start, WorkDayend)
      EndDayMins = DateDiff("n", WorkDayStart, Finish)
      'adjust for time entries outside of business hours
    
    
      'Calculate total hours and days between start and end times
    
      intGrossDays = DateDiff("d", (Start), (Finish))
      intGrossMins = DateDiff("n", (Start), (Finish))
    
      'count number of weekend days and holidays (from a table called "Holidays" that lists them)
    
      For i = 0 To intGrossDays
        dteCurrDate = Start + i
        If Weekday(dteCurrDate, vbSaturday) < 3 Then
          If i = 0 Then    'first day is a weekend, don't count
            StartDayMins = 0
          ElseIf i = intGrossDays Then    'last day is a weekend, don't coung
            EndDayMins = 0
          Else    'subtract a day
            nonWorkDays = nonWorkDays + 1
          End If
        Else
          If Not IsNull(DLookup("[HolDate]", "Holidays", "[HolDate] = #" & Int(dteCurrDate) & "#")) Then
            If i = 0 Then    'first day is a holiday, don't count
              StartDayMins = 0
            ElseIf i = intGrossDays Then    'last day is a holiday, don't coung
              EndDayMins = 0
            Else    'subtract a day
              nonWorkDays = nonWorkDays + 1
            End If
          End If
        End If
      Next i
      'Calculate number of work hours
    
      Select Case intGrossDays
        Case 0
          'start and end time on same day
          NetworkMins = (intGrossMins - ((nonWorkDays) * 1440))
        Case 1
          'start and end time on consecutive days
          NetworkMins = StartDayMins + EndDayMins
        Case Is > 1
          'start and end time on non consecutive days
          NetworkMins = (((intGrossDays - 1) - nonWorkDays) * 480) + (StartDayMins + EndDayMins)
      End Select
      If Spellout = True Then
        NetWorkhours = MinsToTime(NetworkMins)    ' hours and mins
      Else
        NetWorkhours = NetworkMins    ' minutes only
      End If
    
    End Function
    Paul

  7. #7
    Join Date
    Aug 2009
    Posts
    61
    okay that worked for the ates between 8/1/11 and 8/2/11. It now does not count that date. However, I am still able to enter 8/1/11 8:00 AM until 8/1/11 12:00 PM and it returns 4, but it should return 0.

  8. #8
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    I only have Access 2000 on this PC, and I turned off my laptop with the virtual machine I was using to work on your db. I'll be at another PC in a little bit with 2007 on it so I'll look at it then.
    Paul

  9. #9
    Join Date
    Jun 2011
    Location
    Inside your mind
    Posts
    297
    Can't open your DB at work (which is a MASSIVE pain tbh), but I would DEFINITELY go for validating the input...

    Checking that the start and end date entered is not between two existing dates/times will give you much less of a headache than trying to use this setup.


    As I can't see your Db, can you just describe a few of the features to me, including their control names (i.e. combobox322, etc):

    What are the start date and end date inputs?
    What is the name of the button (assuming you use a submit-esq button)?
    What are the names of the Table(s) involved?
    What are the names of the Fields involved (and on which tables do they reside)?


    Sorry to be a pain about it, blame this dodgy network!
    Looking for the perfect beer...

  10. #10
    Join Date
    Aug 2009
    Posts
    61
    What are the start date and end date inputs?
    I have four fields: [StartDate], [StartTime], [EndDate], [FinishTime]. All o fthere are date/time format. I then use the query to join the fields into one date/time field. Seems wierd, but I have found that people get confused about how to change the time portion of a date/time field.

    What is the name of the button (assuming you use a submit-esq button)?
    I am not using a button to do the calculation, it is ran from a query.

    Code:
    SELECT VacRequests.Employee, [StartDate] & " " & [StartTime] AS Start, [EndDate] & " " & [FinishTime] AS Finish, VacRequests.LeaveType, NetWorkhours([Start],[Finish],True) AS [Time Taken], DateDiff("yyyy",[EffectiveStartDate],Now()) AS Service, VacDays([Service]) AS [Vac Allowance]
    FROM VacRequests INNER JOIN Employees ON VacRequests.Employee = Employees.EmployeeID;

    What are the names of the Table(s) involved?
    VacRequests - this table lists the name, the start and end date/time, the leave type, when it was requested, the approval, and any comments.

    Employees - employees information table.

    What are the names of the Fields involved (and on which tables do they reside)?
    From the VacRequests Table:

    StartDate
    StartTime
    EndDate
    FinishTime
    Employee

    From the employees table:

    I am using this table to calculate vacation balance, so it is not really involved in this calculation.

  11. #11
    Join Date
    Jun 2011
    Location
    Inside your mind
    Posts
    297
    What is the name of the button (assuming you use a submit-esq button)?
    I am not using a button to do the calculation, it is ran from a query.
    When I asked about a button, I was referring to the input that the user does.

    i.e. I imagine you have a form with 4 input areas, and a button they push to request the holiday.

    Wish I could get this PC sorted out so I could view the DB, would make life much easier.
    Looking for the perfect beer...

  12. #12
    Join Date
    Aug 2009
    Posts
    61
    Ahh sorry.

    The form has two text boxes for the request dates (start and end) and two combo boxes for the request times (start and finish). I am using combo boxes for these to limit what times they can request because vacation can only be taken in 4 hour chunks.

    My approval at this point is basically through a query that looks for records that are neither approved or denied.

    I have thought about the button, but have not put it into action yet.

  13. #13
    Join Date
    Jun 2011
    Location
    Inside your mind
    Posts
    297
    Well, that button I would use to verify that the dates are acceptable.

    Trying to work it into a query is pretty complex, and quite unnecessary.

    I'll have a look at the Db when I get home, but as you seem to be pretty competent with VBA and SQL, I'd suggest you take a look at simplifying your SQL query and assigning a basic bit of VBA verification when they try to submit their proposed dates.

    If they don't fall between existing holiday:
    Then book it

    Otherwise, if they do:
    Use a MsgBox to inform them that their proposed dates overlap with x, where x is some holiday already booked.


    Do you follow me alright?
    Looking for the perfect beer...

  14. #14
    Join Date
    Aug 2009
    Posts
    61
    May be I come across smarter than I am. I am still pretty novice at VBA, but learning.

    I understand what you are saying though. Would that involve using a Dlookup to verify the dates?

  15. #15
    Join Date
    Aug 2009
    Posts
    61
    I tried to put together and if statement to test the dates, but not suprisingly I do not get any results other than #ERROR

    I am trying to say that if the date falls on one of the [Holdate]'s in the "holidays" table, then ignore the date, and if not, take the date entered.

    Can you take a look and tell me why this does not work?

    Code:
    Public Function CheckDates(Start As Date) As String
    
    If startDate = (DLookup("[HolDate]", "Holidays", "[HolDate]")) Then
    Start = startDate + 1
    Else
    startDate = Start
    End If
    
    End Function

Posting Permissions

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