DateDiff Help! 1

[NavVet]

Hello everyone!,

I am having a bit of trouble producing the results I need. I am trying to assign a tier level based on the persons hire date. If they have been here less than four years from today's date it will assign a "1" value or else "2". What I have below is not producing the results I need and I believe it has something do with the format "YYYY" and I don't think i have this formatted correctly. Here is an example of the hire date showing an incorrect tier assignment. The difference between the two dates is greater than 4 years and therefore should be assigned a level 2.


Hire Date Date() Tier
1/2/2013 8/9/17 1

TierLevel: IIf(DateDiff("yyyy",[SomeTable]![HireDate],Date())<4,"1","2")

Thanks!

 

[Andrzejek]

DateDiff Function returns a Variant (Long) specifying the number of time intervals between two specified dates
So you get 4 or 5 or 6 etc (rounded)., but not 4.5 or 3.75 or 5.02

Are you going to store the Tier value in your table?

Have fun.

---- Andy

There is a great need for a sarcasm font.

 

[NavVet]

Yes I will be storing the Tier in my table. Basically, if the employee has been here over 4 years (based on their hiredate) assign a Tier "2" and if less than 4 years assign "1' to the Tier Level.

 

[dhookom]

I don't think you want to use DateDiff() with years since:
Dates one day or 730 days apart can return the same value:

 DateDiff("yyyy",#12/31/2016#,#1/1/2017# ) = 1
DateDiff("yyyy",#1/1/2016#,#12/31/2017# ) = 1

If you are looking for a function or expression that returns something similar to an age calculation use this function from theAccessWeb.

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[NavVet]

Thank you for your reply. I will read up on your suggestion.

 

[Andrzejek]

I will be storing the Tier in my table" - well, don't.
Today Employee1 has been working for the Company for 3.5 years (Tier 1), so in 6 months it will be 4 years (Tier 2). How often are you going to update this table?

Have fun.

---- Andy

There is a great need for a sarcasm font.

 

[dhookom]

In addition to Andy's great comment about storing and maintaining the Tier, I would question the practice of hard-coding tier level thresholds in code/expressions rather than in a table. What happens when the tier levels change? Do you plan on finding all of the expressions where this is calculated and update them all?

The 4 years and Tier 1 and 2 belong in a small table where a user can modify them as business rules change. Minimally I would create small user-defined functions in a module named "modBusinessCalcs" like:

Function GetYears(datFrom As Date, datTo As Date) As Integer
[highlight #FCE94F]    ' Returns the years between 2 dates
    ' Doesn't handle negative date ranges i.e. datFrom > datTo
    
    'datFrom is the starting/earlier date
    'datTo is the end/current date
    'Call this function like any other function
    '   CurrentAge = GetYears([DateOfBirth],Date())
    '   EmploymentYears = GetYears([DateOfHire],[DateOfRetire])[/highlight]
    
    If Month(datTo) < Month(datFrom) Or (Month(datTo) = _
                Month(datFrom) And Day(datTo) < Day(datFrom)) Then
            GetYears = Year(datTo) - Year(datFrom) - 1
    Else
            GetYears = Year(datTo) - Year(datFrom)
    End If
End Function
Public Function GetTier(datFrom As Date, datTo As Date) As Integer
[highlight #FCE94F]    'Function to return the Tier value based on the years between datFrom and datTo
    'Call this function like any other function
    '   Tier = GetTier([DateOfHire],Date())[/highlight]
    
    Dim intYears As Integer
[highlight #FCE94F]    ' First get the number of years between the dates[/highlight]
    intYears = GetYears(datFrom, datTo)
    Select Case intYears
        Case Is < 4
            GetTier = 1
        Case Else
            GetTier = 2
    End Select
End Function

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[NavVet]

What a excellent thought! This is a much better way of handling these updates.

Thank you all for your time; Much Appreciated!

 

[dhookom]

I should have given credit for the GetYears() function to theAccessWeb.

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016