Solved Between Two Dates - Display Years and Months (1 Viewer)

[vcarrill]

I am building an employee database. Under the field "Seniority" I need it to display their tenure in Years and Months, (days) is optional.

I have a field with the date of hire (DOH) for every employee.

How may I achieve this?

I am using Access 2010.

Thank you

 

[Dreamweaver]

Hope tis is what your looking for

Date/Time: Calculate Age of a person

www.theaccessweb.com

 

[theDBguy]

Hi. You could also try this one.

A More Complete DateDiff Function

 

[Dreamweaver]

I've done something like that with my Employee Example V4

 

[vcarrill]

Hi. You could also try this one.

A More Complete DateDiff Function

Hello, where and how would I use the code?

Thank you

 

[theDBguy]

Hello, where and how would I use the code?

Thank you

Hi. You would copy and paste the code in a Standard Module and just call the function wherever you want to display the years and months.

 

[vcarrill]

Hi. You would copy and paste the code in a Standard Module and just call the function wherever you want to display the years and months.

Once this is done, where would I then place the entered "function"? Under Property Sheet, On Click, or? Also how does the code know to look at the date I have under DOH? I only have DOH, I do not have a field for Now(). Do I need to have it or can the formula calculate between DOH and current date?

Appreciate your patience!

 

[theDBguy]

Once this is done, where would I then place the entered "function"? Under Property Sheet, On Click, or? Also how does the code know to look at the date I have under DOH?

Appreciate your patience!

I would recommend using the function in a query as a calculated column. Let's say, for example, you have a form displaying employee info and it's based on the table Employees. You can create a query based on the same table and add the YearMonth column. For example:

SELECT Employees.*, Diff2Dates("ym",[DOH],Date()) As YM FROM Employees

Then, add the YM field to your form. Hope that helps...

 

[vcarrill]

I would recommend using the function in a query as a calculated column. Let's say, for example, you have a form displaying employee info and it's based on the table Employees. You can create a query based on the same table and add the YearMonth column. For example:

SELECT Employees.*, Diff2Dates("ym",[DOH],Date()) As YM FROM Employees

Then, add the YM field to your form. Hope that helps...

Hello,

I followed your instructions and it is working great! The only issue I am having is adding the YM to the Access form. I look at Add Existing Fields and it is not an option.

How do I add the YM from the query to the form?

Thank you

 

[theDBguy]

Hello,

I followed your instructions and it is working great! The only issue I am having is adding the YM to the Access form. I look at Add Existing Fields and it is not an option.

How do I add the YM from the query to the form?

Thank you

What is the Record Source of your Form? If it's the name of the Table, try changing it to the name of the Query. Then, check the Field List, and you should be able to see YM to add to the Form.

 

[vcarrill]

You Sir are a legend! Thank you!

 

[theDBguy]

You Sir are a legend! Thank you!

Hi. You're welcome. Glad we could assist. Good luck with your project.