When specific questions are asked, we expect those questions to be answered directly, rather than indirectly answering it with another explanation. The questions asked wasn't because I didn't understand what you were doing, but because the code I was going to write needed those specific points clarified. Remember this for future posts.
Here's some code that should give you some head start:
You will notice that it's expecting a Grandfathered field as well and that's because that's how your table should be designed. At the moment the PTODescID field in tblEmployees isn't correctly set up. What you want is a field that indicates whether an employee is grandfathered in or not. tblPTODesc should just be there for reference.
There are more complex ways of setting this up but the function is an easier way out.
Feel free to ask any questions.
Here's some code that should give you some head start:
Code:
Public Function PTOHours(DteHire As Date, GrandFathered As Variant) As Variant
If IsNull(GrandFathered) Then
' Is not grandfathered in
Select Case DateDiff("yyyy", DteHire, Date)
Case Is >= 10
PTOHours = 184
Case Is >= 5
PTOHours = 144
Case Is >= 1
PTOHours = 104
Case 0 And Month(DteHire) <= 6
PTOHours = 40
End Select
Else
' Is grandfathered in
Select Case DateDiff("yyyy", DteHire, #12/31/2012#)
Case Is >= 20
PTOHours = 240
Case Is >= 10
PTOHours = 200
Case Is >= 5
PTOHours = 160
Case Is >= 0
PTOHours = 120
End Select
End If
End Function
There are more complex ways of setting this up but the function is an easier way out.
Feel free to ask any questions.