How to count consecutive vacation dates for employees in text box?

[wordbrew]

Hello all,

I’m beginning to get a pretty good foundation in VBA, but I can’t wrap my head around this problem, even after trolling around the net searching through other posts that seemed remotely related. So I am hopeful that some (always) awesome and helpful Bytes brain power can be thrown my way.

If it’s even possible, I would like to be able to count the vacation dates that my employees have taken consecutively (doesn’t matter if they are holidays or on the weekend, just all days listed as vacation and taken consecutively).

In a form titled [frmVacations], I have a subform titled [tblVacationsubform] which allows me to input and show vacation dates taken or scheduled for each employee. The relevant fields in the subform are titled [EmployeeName], [VacationType] from which I can choose v/d (for vacation day) d/d (for discretionary day) s/o (for scheduled off day) etc…. I’m only concerned about counting “v/d”. And lastly of course I have a field [VacationDate]. The subform is pulling it’s information from a table titled [tblVacations].

The reason for this being that, for example, if an employee has 10 years with the company he/she gets 4 weeks of vacation. 2 of these weeks can be broken up and taken in intermittent individual days, but the other 2 weeks needs to be taken together in solid blocks (i.e 5 days taken consecutively). So this is needed in order to keep better track and make sure the employee isn’t accidently breaking up more vacation weeks than is allowed.

I already have a text box able to comb through a query titled [tblVacations vac query] and count how many “v/d” are taken for each employee, but I would love to have a text box (which I have created and titled [txtConsecDays] ) be able to look through the table/recordset or query and count how many days are being taken consecutively in blocks of 5. My ultimate goal would be to have a text box reflect “1” (week) if an employee has taken 5 days together consecutively, and then “2” (weeks) if they have taken another 5 days together after that, etc... This would make things so much easier to track and avoid slip ups when scheduling vacations for over 30 employees.

I do have employees who only work 4 days a week, and therefore a vacation week for them obviously only consists of a block of 4 days, but this is a problem I would try to tackle later when/ if someone can help me with this first main problem.

Hopefully I’ve been clear in what I need and have included all the information needed if this scenario is even possible to implement. I did try to mess around with queries for this problem but again seemed to be running in circles, and ultimately realized this problem probably had to be done in VBA. As always, I really appreciate any insights and suggestions anyone can help with.

[gnawoncents]

How is the data in the [VacationDate] field formatted?

[wordbrew]

Thanks for taking a look at this gnawoncents.

In the table [tblVacations] that the subform is pulling from, the data type [VacationDate] is formatted in is Date/Time.

[gnawoncents]

Is only one day assigned to each record? In other words, would a series of records look something like:

EmployeeName..VacationType..VacationDate
------------ ------------ ------------
Doe, John.....v/d...........12 Jan 10
Doe, John.....v/d...........13 Jan 10
Doe, John.....v/d...........14 Jan 10
Doe, John.....v/d...........15 Jan 10
Doe, John.....v/d...........16 Jan 10
Smith, Jane...v/d...........1 Jan 10
Smith, Jane...v/d...........3 Jan 10
Smith, Jane...v/d...........2 Feb 10
Smith, Jane...v/d...........3 Feb 10
Smith, Jane...v/d...........4 Feb 10
Smith, Jane...v/d...........5 Feb 10

...And you would count John Doe as having one week and Jane Smith as zero? If this isn't your setup, please provide an example of your data.

[wordbrew]

gnawoncents you have it exactly correct. Only one day is assigned to each record and the layout is just as you put in your example.

And yes, for my needs, what John Doe has would constitute a full week block, but Jane Smith (if she is a five day a week employee) having only 4 days together consecutively still wouldn't count as a full week since she has a fith day she is essentially breaking up and taking apart from the others.

If she were a 4 day a week employee (of which I actually do have 4 employees who meet this criteria) then those 4 days would constitute a full week block. But since 90% of my employees are 5 day a week, I'm just trying to tackle this problem first lol. (I do have a text box titled [txtNumberDaysWorkedPerWeek] that other texts boxes on the form look at and then return values telling things such as the number of vacation days they are owed when taking into account [txtNumberDaysWorkedPerWeek]*[WeeksOfVacation], etc...)

Thank you again for lending your time looking into this.

[gnawoncents]

Okay, this is definitely doable. I have a few more questions:

1) Do you have an employees table (only one instance of each employee) to which we could update the consequtive weeks. If so, what is its name, and the applicable field names?
2) When do you want the reporting to start/stop counting (e.g. calander year, fiscal year, dynamic dates, etc.)
3) Where are you storing the data on how many days an individual works per week
4) Does a five day stretch broken up by a weekend or holiday still count? For example: taking Jan 1,2,3,6, & 7 off where the fourth and fifth are a weekend.

[wordbrew]

Wow. It would make me so happy if this were possible. You would = awesomeness. :)

1) I do have an employees table to store only one instance of each employee. It's simply titled [tblEmployees]. This includes the fields [NumberDaysWorkedPerWeek], [HireDate], [GemsID](which I should have included as relevant in the [tblVacationsubform] since it is a primary key), and [Last]. The table [tblVacations] which the subform pulls from uses [Last] in [tblEmployees] as a lookup field for [EmpoyeeName] in [tblVacations].

2) I want the counting to reset each calendar year. I have the query that can count up the total "v/d" that an employee has accrued doing this. In a column with the expression Year([tblVacations]) I have the criteria Year(Date()) So that when it hits 2011, all of the counts return to zero and start over.

3) The data is stored in [tblEmployees] in the field [NumberDaysWorkedPerWeek].

4) A five day stretch broken up by a weekend or holiday does not count. Example being that a Monday-Friday employee has to take the whole week Monday through Friday. We have some people that work Wednesday through Sunday, so same goes for them. They have to take their week Wednesday through Sunday. So hopefully this will simplify the problem cause it takes having to account for holidays or weekends out of the equation. Only five consecutive days would count.

And you probably assumed this, but the main form titled [frmVacations] has a combo box titled [Combo21] (I totally should have titled that something more interesting!) that looks up the employee names, that is linked to the subform. So when I select John Doe for example, only John Doe's info shows up in the subform.

I hope all of this has been helpful!

[gnawoncents]

Okay, I think I've got it.

I have a form with Record Source set to tblEmployees and two controls: Combo21 and txtConsecDays.

Both are unbound. Combo21 has the following Row Source:

SELECT tblEmployees.Last FROM tblEmployees WHERE (((tblEmployees.Last) Is Not Null)); 

So far, this should be the same as your current setup.

Now, I added the code below to the After Update event of Combo21. This will filter for the employee you selected, looking only for vacation days (v/d) taken in the current calendar year. It then counts consecutive days and matches each set against how many days the employee works per week. Finally, the number of "weeks" is sent to the txtConsecDays field.

Private Sub Combo21_AfterUpdate()
 
DoCmd.SearchForRecord , , , "[Last] = '" & [Screen].[ActiveControl] & "'" 'Sets the form to the record for employee selected
 
'Set up your data
Dim dbWEEKS As Database
Dim rsWEEKS As Recordset
Dim strSQL As String
Dim intDaysPerWeek As Integer
Dim datePrevious As Date
Dim intConsecutive As Integer
Dim intWEEKS As Integer
 
'Set up the SQL string to use for opening the recordset
strSQL = "SELECT tblVacations.VacationDate, tblVacations.EmployeeName " & _
        "FROM tblVacations " & _
        "WHERE (tblVacations.EmployeeName = '" & Me.[Combo21] & "') " & _
        "AND (tblVacations.VacationDate Like '*" & Format(Date, "yyyy") & "') " & _
        "AND (tblVacations.VacationType = 'v/d') " & _
        "ORDER BY tblVacations.VacationDate;"
 
Set dbWEEKS = CurrentDb
Set rsWEEKS = dbWEEKS.OpenRecordset(strSQL)
 
intDaysPerWeek = DLookup("[NumberDaysWorkedPerWeek]", "tblEmployees", "[Last] = '" & Me.Combo21 & "'") 'Lookup how many days the employee works per week
datePrevious = 1
intConsecutive = 0
intWEEKS = 0
 
'Check to see if there are any applicable records
If rsWEEKS.RecordCount = 0 Then
    MsgBox "Employee has not taken any vacation days this year", vbInformation, "No Records Found"
    Exit Sub
End If
 
rsWEEKS.MoveLast
rsWEEKS.MoveFirst
 
  'Count how many sets of consecutive vacation days count as a week for the employee
  Do While Not rsWEEKS.EOF
    If (datePrevious + 1) = rsWEEKS.Fields!VacationDate Then
        intConsecutive = intConsecutive + 1
        If intConsecutive = intDaysPerWeek Then
            intWEEKS = intWEEKS + 1
        End If
      Else
        intConsecutive = 0
    End If
    datePrevious = rsWEEKS.Fields!VacationDate
    rsWEEKS.MoveNext
  Loop
 
Me.txtConsecDays = intWEEKS 'Put the total in the text box
 
'Close what was opened
rsWEEKS.Close
Set dbWEEKS = Nothing
 
End Sub 

Please let me know if this works for you and/or you have any questions.

[wordbrew]

Wow gnawoncents, I can already see the brilliant way you are tackling my problem. I'm still encountering problems, but I fear they are a lack of information on my part.

My row source for Combo21 looks like this:
SELECT tblEmployees.GemsID, [Last] & ", " & [First] AS Expr1 FROM tblEmployees ORDER BY [Last] & "," & [First];

I don't think I included the [First] as relevant to you before because i didn't realize the route you were going to take. I only included [Last] because in the subform, it's all I have listed, but in the combo box I had opted to have it pull the first name as well just to make things look pretty.

Also, and this may be related to my mix-up, but when I use the combo box after inputing your code in the after update, it gives me an error code of "Compile error: Method or Data member not found" and highlights the
.[ActiveControl] portion of [Screen].[ActiveControl]

I thank you so much for your time, patience and effort on all of this. I'm learning so many things just by watching you take on this problem.

FYI, the [gemsID] it is referring to in the combo is the primary key in [tblEmployees]. Every employee gets a unique 7 digit code called a GEMS ID, so that's why I set it as the primary key.

[gnawoncents]

wordbrew,

Is the gemsID numeric or text? Also, is it in both the vacations and employees tables?

[gnawoncents]

Okay,

I made a few tweaks based on your information, and the ASSUMPTION that GemsID exists in both tables. Please let me know if it works for you.

Private Sub Combo21_AfterUpdate()
 
DoCmd.SearchForRecord , , , "[GemsID] = '" & Me.Combo21 & "'" 'Sets the form to the record for employee selected
 
'Set up your data
Dim dbWEEKS As Database
Dim rsWEEKS As Recordset
Dim strSQL As String
Dim intDaysPerWeek As Integer
Dim datePrevious As Date
Dim intConsecutive As Integer
Dim intWEEKS As Integer
 
'Set up the SQL string to use for opening the recordset
strSQL = "SELECT tblVacations.VacationDate, tblVacations.EmployeeName " & _
        "FROM tblVacations " & _
        "WHERE (tblVacations.GemsID = '" & Me.[Combo21] & "') " & _
        "AND (tblVacations.VacationDate Like '*" & Format(Date, "yyyy") & "') " & _
        "AND (tblVacations.VacationType = 'v/d') " & _
        "ORDER BY tblVacations.VacationDate;"
 
Set dbWEEKS = CurrentDb
Set rsWEEKS = dbWEEKS.OpenRecordset(strSQL)
 
intDaysPerWeek = DLookup("[NumberDaysWorkedPerWeek]", "tblEmployees", "[GemsID] = '" & Me.Combo21 & "'") 'Lookup how many days the employee works per week
datePrevious = 1
intConsecutive = 0
intWEEKS = 0
 
'Check to see if there are any applicable records
If rsWEEKS.RecordCount = 0 Then
    MsgBox "Employee has not taken any vacation days this year", vbInformation, "No Records Found"
    Exit Sub
End If
 
rsWEEKS.MoveLast
rsWEEKS.MoveFirst
 
  'Count how many sets of consecutive vacation days count as a week for the employee
  Do While Not rsWEEKS.EOF
    If (datePrevious + 1) = rsWEEKS.Fields!VacationDate Then
        intConsecutive = intConsecutive + 1
        If intConsecutive = intDaysPerWeek Then
            intWEEKS = intWEEKS + 1
        End If
      Else
        intConsecutive = 0
    End If
    datePrevious = rsWEEKS.Fields!VacationDate
    rsWEEKS.MoveNext
  Loop
 
Me.txtConsecDays = intWEEKS 'Put the total in the text box
 
'Close what was opened
rsWEEKS.Close
Set dbWEEKS = Nothing
 
End Sub 

[wordbrew]

gnawoncents,

In order to cut down on confusion (which is my fault), and to not waste your valuable time (cause you've been awesome devoting so much of it helping me), I've gone ahead and included my database in this thread.

Now this is obviously a stripped down version for many reasons, mainly because the normal version couldn't be zipped down enough to be posted, but you should have all you need. The crew van records and 1DA Crew Van Calendar button on the main intro form won't work because I had to delete the underlying forms to cut down on file size. But the 2DA Crew Van Calendar is all I'm concerned with for now and is what you have been helping me with. For company reasons I've had to remove any logos and replaced any employee information with false names and GemsID's, etc... but it has the full breadth of vacation time in there for you to see.

This is based around a wonderful calendar database shell that ADezei has posted around numerous times (so mad mad props to him!), but of course I have modified it heavily for my purposes.

As for the Gems ID it is text. I would rather it had been numeric, but many of the Gems have to be numbers such as 0987090, and trying to have it numeric kept stripping away the initial zero. So I had to make it text based. (which if there was a way around that lol, please enlighten me!)

As you'll see, the GemsID is in both the tblEmployees and tblVacations. In tblVacations, I named it GemsIDlookup just to know which one I was dealing with at a glance.

You'll see that the txtConsecDays text box is beside the label "Consecutive Days Taken/Scheduled." I'll actually be renaming that "Full Weeks (of consecutive days) Taken/Scheduled" or some such. You'll also see a text box below that with the label beside it titled "Cosecutive Vacations Days List:" This doesn't do anything now, but when you tackled my first problem, my next hope was to eventually get this to list the actual dates of the consecutive days (full weeks) that will be flagged by your solution. Again, this may not be possible or would require too much extra time on your part, and if so please disregard, but I just wanted to give you an FYI on why that text box was yet to do anything.

This database is what it looks like before I tried inserting any of your code into it, so it is pristine so to speak. I figure this way, you'll be able to work the problem, and then post to me what you ended up figuring out to put in the Combo21 AfterUpdate. I'm still young in the ways of VBA so i can't wait to pick through your solution so I can truly understand. I hope to be able to have a firm enough foundation in VBA soon to be able to create solutions like yours from the ground up myself. But if it weren't for brains like yours and the rest of the Bytes crew I wouldn't have the foundation that I do now.

So thank you again for your patience in helping me, it is extremly appreciated!