469,272 Members | 1,587 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,272 developers. It's quick & easy.

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

wordbrew
Hello all,

Im beginning to get a pretty good foundation in VBA, but I cant 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 its even possible, I would like to be able to count the vacation dates that my employees have taken consecutively (doesnt 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. Im only concerned about counting v/d. And lastly of course I have a field [VacationDate]. The subform is pulling its 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 isnt 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 Ive 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.
Nov 4 '10 #1
11 4441
gnawoncents
214 100+
How is the data in the [VacationDate] field formatted?
Nov 4 '10 #2
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.
Nov 4 '10 #3
gnawoncents
214 100+
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.
Nov 5 '10 #4
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.
Nov 5 '10 #5
gnawoncents
214 100+
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.
Nov 5 '10 #6
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!
Nov 5 '10 #7
gnawoncents
214 100+
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:
Expand|Select|Wrap|Line Numbers
  1. 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.

Expand|Select|Wrap|Line Numbers
  1. Private Sub Combo21_AfterUpdate()
  2.  
  3. DoCmd.SearchForRecord , , , "[Last] = '" & [Screen].[ActiveControl] & "'" 'Sets the form to the record for employee selected
  4.  
  5. 'Set up your data
  6. Dim dbWEEKS As Database
  7. Dim rsWEEKS As Recordset
  8. Dim strSQL As String
  9. Dim intDaysPerWeek As Integer
  10. Dim datePrevious As Date
  11. Dim intConsecutive As Integer
  12. Dim intWEEKS As Integer
  13.  
  14. 'Set up the SQL string to use for opening the recordset
  15. strSQL = "SELECT tblVacations.VacationDate, tblVacations.EmployeeName " & _
  16.         "FROM tblVacations " & _
  17.         "WHERE (tblVacations.EmployeeName = '" & Me.[Combo21] & "') " & _
  18.         "AND (tblVacations.VacationDate Like '*" & Format(Date, "yyyy") & "') " & _
  19.         "AND (tblVacations.VacationType = 'v/d') " & _
  20.         "ORDER BY tblVacations.VacationDate;"
  21.  
  22. Set dbWEEKS = CurrentDb
  23. Set rsWEEKS = dbWEEKS.OpenRecordset(strSQL)
  24.  
  25. intDaysPerWeek = DLookup("[NumberDaysWorkedPerWeek]", "tblEmployees", "[Last] = '" & Me.Combo21 & "'") 'Lookup how many days the employee works per week
  26. datePrevious = 1
  27. intConsecutive = 0
  28. intWEEKS = 0
  29.  
  30. 'Check to see if there are any applicable records
  31. If rsWEEKS.RecordCount = 0 Then
  32.     MsgBox "Employee has not taken any vacation days this year", vbInformation, "No Records Found"
  33.     Exit Sub
  34. End If
  35.  
  36. rsWEEKS.MoveLast
  37. rsWEEKS.MoveFirst
  38.  
  39.   'Count how many sets of consecutive vacation days count as a week for the employee
  40.   Do While Not rsWEEKS.EOF
  41.     If (datePrevious + 1) = rsWEEKS.Fields!VacationDate Then
  42.         intConsecutive = intConsecutive + 1
  43.         If intConsecutive = intDaysPerWeek Then
  44.             intWEEKS = intWEEKS + 1
  45.         End If
  46.       Else
  47.         intConsecutive = 0
  48.     End If
  49.     datePrevious = rsWEEKS.Fields!VacationDate
  50.     rsWEEKS.MoveNext
  51.   Loop
  52.  
  53. Me.txtConsecDays = intWEEKS 'Put the total in the text box
  54.  
  55. 'Close what was opened
  56. rsWEEKS.Close
  57. Set dbWEEKS = Nothing
  58.  
  59. End Sub 
Please let me know if this works for you and/or you have any questions.
Nov 5 '10 #8
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.
Nov 6 '10 #9
gnawoncents
214 100+
wordbrew,

Is the gemsID numeric or text? Also, is it in both the vacations and employees tables?
Nov 9 '10 #10
gnawoncents
214 100+
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.

Expand|Select|Wrap|Line Numbers
  1. Private Sub Combo21_AfterUpdate()
  2.  
  3. DoCmd.SearchForRecord , , , "[GemsID] = '" & Me.Combo21 & "'" 'Sets the form to the record for employee selected
  4.  
  5. 'Set up your data
  6. Dim dbWEEKS As Database
  7. Dim rsWEEKS As Recordset
  8. Dim strSQL As String
  9. Dim intDaysPerWeek As Integer
  10. Dim datePrevious As Date
  11. Dim intConsecutive As Integer
  12. Dim intWEEKS As Integer
  13.  
  14. 'Set up the SQL string to use for opening the recordset
  15. strSQL = "SELECT tblVacations.VacationDate, tblVacations.EmployeeName " & _
  16.         "FROM tblVacations " & _
  17.         "WHERE (tblVacations.GemsID = '" & Me.[Combo21] & "') " & _
  18.         "AND (tblVacations.VacationDate Like '*" & Format(Date, "yyyy") & "') " & _
  19.         "AND (tblVacations.VacationType = 'v/d') " & _
  20.         "ORDER BY tblVacations.VacationDate;"
  21.  
  22. Set dbWEEKS = CurrentDb
  23. Set rsWEEKS = dbWEEKS.OpenRecordset(strSQL)
  24.  
  25. intDaysPerWeek = DLookup("[NumberDaysWorkedPerWeek]", "tblEmployees", "[GemsID] = '" & Me.Combo21 & "'") 'Lookup how many days the employee works per week
  26. datePrevious = 1
  27. intConsecutive = 0
  28. intWEEKS = 0
  29.  
  30. 'Check to see if there are any applicable records
  31. If rsWEEKS.RecordCount = 0 Then
  32.     MsgBox "Employee has not taken any vacation days this year", vbInformation, "No Records Found"
  33.     Exit Sub
  34. End If
  35.  
  36. rsWEEKS.MoveLast
  37. rsWEEKS.MoveFirst
  38.  
  39.   'Count how many sets of consecutive vacation days count as a week for the employee
  40.   Do While Not rsWEEKS.EOF
  41.     If (datePrevious + 1) = rsWEEKS.Fields!VacationDate Then
  42.         intConsecutive = intConsecutive + 1
  43.         If intConsecutive = intDaysPerWeek Then
  44.             intWEEKS = intWEEKS + 1
  45.         End If
  46.       Else
  47.         intConsecutive = 0
  48.     End If
  49.     datePrevious = rsWEEKS.Fields!VacationDate
  50.     rsWEEKS.MoveNext
  51.   Loop
  52.  
  53. Me.txtConsecDays = intWEEKS 'Put the total in the text box
  54.  
  55. 'Close what was opened
  56. rsWEEKS.Close
  57. Set dbWEEKS = Nothing
  58.  
  59. End Sub 
Nov 9 '10 #11
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!
Attached Files
File Type: zip MyDatabaseCalendarSample.zip (3.56 MB, 137 views)
Nov 10 '10 #12

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

10 posts views Thread by ChrisD | last post: by
5 posts views Thread by krishnakant Mane | last post: by
9 posts views Thread by sha2484 | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by suresh191 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.