Calculating a date using DateDiff Function

[BSC5905]

I’m trying to calculate the length of service of employees by using the DateDiff Function in a query and have the results displayed in a report. The expression that I am using is:

Length of Service: DateDiff("yyyy",[tblDA]![HireDate], Now())

The expression works fine and displays in the report fine. I was wondering is there a way to show the year and months of service. For instance 1.5yrs or 1yr 6months. If possible I would like the data to show 1 yr 6months.

 

[dhookom]

You should probably not be using DateDiff("yyyy",,) unless you realize that
DateDiff("yyyy",#1/1/2005#,#12/31/2006#)
and
DateDiff("yyyy",#12/31/2005#,#1/1/2006#)
return the same value.

Doug Steele has a more flexible datediff function at

http://www.accessmvp.com/DJSteele/Diff2Dates.html.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]