Solved Finding Tenure Time

[Feral]

Hello!

I am at my wit's end and need some help understanding on how to fix or improve a Function to implement into a query.

I am trying to find how long an employee has been working at my company but expressed in either Years or Months only. Not both. For example, if an employee has been working for 2 years and 7 months, I want the result to say, "2 years," and if they have worked 0 year and 4 months, I want it to say, "4 months."

Based off of reading other posts trying to find ages using DOB, I attempted to build something similar. I'm very much new to this so please forgive my mistakes. This is why I'm looking for help.

The date we will be referencing is [Hire_Date]. There are no other dates associated with an employee's file.

My understanding of the module is as follows:

Public Function CalcAge (Hire_Date As Date) As String
Dim intYears As Integer
Dim intMonths As Integer

intYears = intMonths / 12
intMonths = intMonth Mod 12

intMonths = DateDiff("m", [Hire_Date], Date())

If intYears > 0 Then
CalcAge = intYears
If intYears > 1 Then
CalcAge = CalcAge & "years"
Else
CalcAge = CalcAge & "year"
End If


If intMonths > 0 Then
CalcAge = intMonths
If intMonths> 1 Then
CalcAge = CalcAge & "months"
Else
CalcAge = CalcAge & "month"
End If
End Function

From here, I know I'm pretty sure I'm missing an IF statement to have one or the other but I'm not sure how to write that.

When trying to place the Function in a Query Field, it gives me a parameter window and then pulls nothing up, so I know I'm not doing something right with how it is above.
In the Field, it's my understanding it should be, "Tenure: CalcAge([Hire_Date])" correct?

I would appreciate any help and would especially love a breakdown if possible or pointed in the right direction.

Thank you all!

 

[theDBguy]

Not sure if this will do what you want but should be a good place to start.

A More Complete DateDiff Function

 

[Feral]

Yes, that probably would help wouldn't it.

intMonths = DateDiff("m", [Hire_Date], Date())


This would be it, right?

 

[theDBguy]

Yes, that probably would help wouldn't it.

intMonths = DateDiff("m", [Hire_Date], Date())


This would be it, right?

It would actually be "Diff2Dates," not "DateDiff," which is the built-in version.

 

[Feral]

It would actually be "Diff2Dates," not "DateDiff," which is the built-in version.

Oh, I'm not familiar with this.

If I put in Date2Diff, would I need to explain that in the code? Not sure the proper phrasing or lingo, my apologies.

 

[Gasman]

You are calculating intyears before you have even obtained intmonths?

If intmonths > 12 then report intyears, else intmonths.

 

[CJ_London]

You have set intyears before you have set intmonths so your if statement is not going to work as expected

Dim intYears As Integer
Dim intMonths As Integer

intYears = intMonths / 12
intMonths = intMonth Mod 12

intMonths = DateDiff("m", [Hire_Date], Date())

If intYears > 0 Then

I would have thought you would get an error

 

[Feral]

Ok so messy all around haha. Oops.

I'm very, very new to coding. Thank you for pointing it out.

Ok, so I would just need to swap the two so "months" is first?

 

[Gasman]

Ok so messy all around haha. Oops.

I'm very, very new to coding. Thank you for pointing it out.

Ok, so I would just need to swap the two so "months" is first?

Put one before the other?

In fact both lines should come after the DateDiff() line.

Plus you should have Option Explict at the top of every module as you have intmonths and IntMonth.

Compile will pickup up typos like that if you have option explicit set.

Search here for that term as to where you need to set for further modukes, but you needc to add it manually for those alreadu created.

 

[ebs17]

Public Function CalcAge_2(Hire_Date As Date) As String
   Dim intYears As Integer

   intYears = DateDiff("yyyy", Hire_Date, Date)
   If intYears < 1 Then
      CalcAge_2 = DateDiff("m", Hire_Date, Date) + (Day(Date) < Day(Hire_Date)) & " months"
   Else
      CalcAge_2 = intYears + (Format(Date, "mmdd") < Format(Hire_Date, "mmdd")) & " years"
   End If
End Function

 

[gemma-the-husky]

I'm not sure offhand, but you would need to check whether datediff("y") rounds values

If not then you could say this sort of pseudocode. Sorry, I can't select a block of text on my phone.

If datediff ("y") >=1 then
Result = datediff("y")
Else
Result = datediff("m")
End if

 

[Feral]

Put one before the other?

In fact both lines should come after the DateDiff() line.

Plus you should have Option Explict at the top of every module as you have intmonths and IntMonth.

Compile will pickup up typos like that if you have option explicit set.

Search here for that term as to where you need to set for further modukes, but you needc to add it manually for those alreadu created.

Oh good catch. Noted! Thank you.

Public Function CalcAge_2(Hire_Date As Date) As String
   Dim intYears As Integer

   intYears = DateDiff("yyyy", Hire_Date, Date)
   If intYears < 1 Then
      CalcAge_2 = DateDiff("m", Hire_Date, Date) + (Day(Date) < Day(Hire_Date)) & " months"
   Else
      CalcAge_2 = intYears + (Format(Date, "mmdd") < Format(Hire_Date, "mmdd")) & " years"
   End If
End Function

May I ask the purpose of the "_2" in the CalcAge_2? Is it just to test as a separate Function? Could you explain this code a bit more please? Thank you!

I'm not sure offhand, but you would need to check whether datediff("y") rounds values

If not then you could say this sort of pseudocode. Sorry, I can't select a block of text on my phone.

If datediff ("y") >=1 then
Result = datediff("y")
Else
Result = datediff("m")
End if

That seems simple enough. How would I incorporate this into the code? Thank you!

 

[ebs17]

purpose of the "_2"

It is just a demarcation from your code.
The name of the function can be anything you want, it just needs to be self-explanatory.

explain this code

DateDiff should be self-explanatory, or compare in the documentation.
However, when it comes to years, it makes a difference whether the Hire_Date is before or after today (regardless of the year).

Therefore, an additional logical expression is used for testing:

(Format(Date, "mmdd") < Format(Hire_Date, "mmdd"))

Its evaluation results in False (0) or True (-1). This number is then added to the DateDiff result.

 

[Feral]

However, when it comes to years, it makes a difference whether the Hire_Date is before or after today (regardless of the year).

Makes sense! Is that why you had Day(Date) there?

 

[ebs17]

Yes, it's the same logic. Day is preferred here because it returns Long, while the Format function produces a String. Comparing using numbers is faster than using strings because fewer bytes are needed for representation.

Format(Date, "mmdd")
' could also be replaced to generate numbers by
Month(Date) * 100 + Day(Date)

 

[Feral]

Yes, it's the same logic. Day is preferred here because it returns Long, while the format function produces a String. Comparing using numbers is faster than using strings because fewer bytes are needed for representation.

Format(Date, "mmdd")
' could also be replaced to generate numbers by
Month(Date) * 100 + Day(Date)

I have a lot to learn! Thank you so much for your time and explaining it to me. I genuinely appreciate it.

Your code also gave me exactly what I wanted as well.

This has given me some insight on what to be aware of and how to improve. Thank you everyone!

 

[Feral]

Public Function CalcAge_2(Hire_Date As Date) As String
   Dim intYears As Integer

   intYears = DateDiff("yyyy", Hire_Date, Date)
   If intYears < 1 Then
      CalcAge_2 = DateDiff("m", Hire_Date, Date) + (Day(Date) < Day(Hire_Date)) & " months"
   Else
      CalcAge_2 = intYears + (Format(Date, "mmdd") < Format(Hire_Date, "mmdd")) & " years"
   End If
End Function

One last question!!! Mostly 'cause I just caught it.

I have an employee who has been here for almost 1 year (will be the 26th) but the code gives back "0 years" instead of "11 months." How would I go about fixing that?

Thank you!

 

[Gasman]

One last question!!! Mostly 'cause I just caught it.

I have an employee who has been here for almost 1 year (will be the 26th) but the code gives back "0 years" instead of "11 months." How would I go about fixing that?

Thank you!

If intYears <= 1 Then

 

[ebs17]

I haven't tested the function sufficient yet. I guess the initial test needs to be changed.

If intYears <1 Then
' =>
If intMonths <13

or something similar, and adjust the other lines of code accordingly. Try it yourself, you learn best by doing.

 

[Feral]

If intYears <= 1 Then

I feel so silly. Excuse me while I wear my dunce cap in the corner.