PTO hours taken and automatically updating a field depending on hire date

[vbaInet]

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:

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

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.

 

[ladyrider696]

So if I understand you correctly, I should have a field in the employees table that states whether an employee is NEWHIRE or GRANDFATHER. Would that field link back to the tblPTODesc table?

 

[vbaInet]

Just "grandfathered". The grandfathered status isn't calculated, it's a status that's earned by merit right? Whereas "new hire" is a calculated status where the hire date is between "1/1" and "30/6".

Only the grandfathered status will link to the employees table. I don't remember what tblPTODesc table holds but I don't think it counts here. I might check it later just to confirm.

 

[vbaInet]

I don't remember what tblPTODesc table holds but I don't think it counts here. I might check it later just to confirm.

Don't implement anything yet. Let me double check the employees and pto tables and I'll advise. The code posted was just to give you an idea on how it could be implemented.

 

[vbaInet]

I don't remember what tblPTODesc table holds but I don't think it counts here. I might check it later just to confirm.

Don't implement anything yet. Let me double check the employees and pto tables and I'll advise. The code posted was just to give you an idea on how it could be implemented.

I'll have a look later today.