Question Can't figure out the string for new vacation policy - please help.

[denileigh]

Happy New Year All!!

Our vacation policy changed with the new year and I am pulling my hair out trying to figure out how to have the database automate the vacation available for our employees. Let me see if I can explain this properly. Previously, vacation was figured based on your annual hire date. Now the first week is figured that way however, after your first year it is done on an annual basis with January 1st being the reset date. So...

Employees receive 40 hours of vacation after their first year of employment.

The following January after completion of their first year of employment they receive 80 hours. For example: An employee hired 1/3/10 would get 40 hours of vacation on 1/3/11 and on 1/1/12 he would be eligible for 80 hours. An employee hired 12/22/10 would receive 40 hours as of 12/22/11 and on 1/1/12 he would be eligible for 80 hours.

I hope I am explaining this properly where it makes sense. I HATE this policy because I don't think it's fair but I lost the argument. Anyway - the remaining breakdown is below:

The following January after completion of their 10th year of employment they receive 120 hours.

The following January after completion of their 20th year of employment they receive 160 hours.

THANKS so much for any help you can give. The formula is easy for me until you throw in the whole January thing and I just can't figure it out.

 

[jdraw]

Happy New Year All!!

Our vacation policy changed with the new year and I am pulling my hair out trying to figure out how to have the database automate the vacation available for our employees. Let me see if I can explain this properly. Previously, vacation was figured based on your annual hire date. Now the first week is figured that way however, after your first year it is done on an annual basis with January 1st being the reset date. So...

Employees receive 40 hours of vacation after their first year of employment.

The following January after completion of their first year of employment they receive 80 hours. For example: An employee hired 1/3/10 would get 40 hours of vacation on 1/3/11 and on 1/1/12 he would be eligible for 80 hours. An employee hired 12/22/10 would receive 40 hours as of 12/22/11 and on 1/1/12 he would be eligible for 80 hours.

I hope I am explaining this properly where it makes sense. I HATE this policy because I don't think it's fair but I lost the argument. Anyway - the remaining breakdown is below:

The following January after completion of their 10th year of employment they receive 120 hours.

The following January after completion of their 20th year of employment they receive 160 hours.

THANKS so much for any help you can give. The formula is easy for me until you throw in the whole January thing and I just can't figure it out.

I tried this code with your examples -seems to work.

You can step through the code using different values to see what the associated vacation should be.

'---------------------------------------------------------------------------------------
' Procedure : TestVacations
' Author    : Jack
' Created   : 1/4/2010
' Purpose   :Employees receive 40 hours of vacation after
'their first year of employment.
'The January following completion of their first year of employment
'they receive 80 hours. For example: An employee hired 1/3/10 would
'get 40 hours of vacation on 1/3/11 and on 1/1/12 he would be eligible for 80 hours.
'An employee hired 12/22/10 would receive 40 hours as of 12/22/11 and on 1/1/12
'he would be eligible for 80 hours.
'
'As of January 1, following completion of their 10th year of employment,
'they receive 120 hours.
'
'As of January 1, following completion of their 20th year of employment,
'they receive 160 hours.

'---------------------------------------------------------------------------------------
' Last Modified:
'
' Inputs: N/A
' Dependency: N/A
'------------------------------------------------------------------------------
'
Sub TestVacations()
Dim stDate As Date  'Start Date
Dim vacDate As Date 'Future date to determine vacation allowance
Dim x As Integer
Dim y As Integer
   On Error GoTo TestVacations_Error
'stDate = #1/3/2010#
stDate = #12/22/2010#
'vacDate = #1/3/2011#
'vacDate = #1/1/2012#
'vacDate = #1/1/2032#
vacDate = #1/1/2011#
'vacDate = #12/22/2011#
y = DateDiff("d", stDate, vacDate)
If y < 365 Then
x = 0
Else
x = DateDiff("yyyy", stDate, vacDate)
End If
 Select Case x
 Case 0
    MsgBox " Vacation = 0 days"
 Case 1
    MsgBox " Vacation = 40 days"
 Case 2, 3, 4, 5, 6, 7, 8, 9
    MsgBox " Vacation = 80 days"
 Case 10, 11, 12, 13, 14, 15, 16, 17, 18, 19
     MsgBox " Vacation = 120 days"
 Case 20, Is > 20
    MsgBox " Vacation = 160 days"
 Case Else
    GoTo TestVacations_Error
 End Select


   On Error GoTo 0
   Exit Sub

TestVacations_Error:

    MsgBox "Error " & Err.number & " (" & Err.Description & ") in procedure TestVacations of Module Module4"

End Sub

 

[denileigh]

Thanks! I'll try....how would I convert that into an if then statement to use in a text box?

 

[jdraw]

Thanks! I'll try....how would I convert that into an if then statement to use in a text box?

A little more info please.

What did you want in the text box?
Where is the data for the StartDate?
What Date will you be using to determine how much vacation Person X has as of that Date?

Did you go thru some of the sample values?

This routine shows an algorithm to use, based on the conditions you provided.

You could make this routine into a function with parameters that could be called/referenced from other code to return the Number of Days of Vacation.

If you supplied StartDate and VacDate to such a function , it would return an Integer value representing the Days of Vacation.


You could change the MsgBox lines in the Case statement, to update a table.



I just found this during casual searching:http://www.access-programmers.co.uk/forums/showthread.php?t=61538

Seems you've been trying to solve this for almost 6 years???? Please advise.

 

[denileigh]

Well, I have and I haven't. They have changed the policy annually. I wish they'd settle on something. I thought they had visited this one previously but I couldn't find that post. THANKS for finding it!!