Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

DateDiff Help! 1

Status
Not open for further replies.

NavVet

Technical User
Aug 9, 2017
4
US
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!

 
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.
 
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.
 
I don't think you want to use DateDiff() with years since:
Dates one day or 730 days apart can return the same value:
Code:
 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
 
Thank you for your reply. I will read up on your suggestion.
 
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.
 
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:

Code:
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
 
What a excellent thought! This is a much better way of handling these updates.

Thank you all for your time; Much Appreciated!
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top