Calculate Whole Years Between Two Dates

[DAVI2703]

I calculate years between two dates
example 01-07-2014 - 10-12-2012
I get answer 2 years but correct answer is 1 year 7 months but i want number of years only. how did solve this problem in access 2007

[NeoPa]

Are you looking for a calculation within a query (SQL) or in code (VBA)?

It helps if you prepare your question properly before posting it.

[NeoPa]

DateDiff() gives a figure that ignores everything but the year. That's not too helpful when you want to the number of whole years :-(

So, we have to get that figure, then subtract one from it if it turns out to be too much.

It's easier to do in VBA so I'll give that example :

Public Function Age(datFrom As Date, datTo As Date) As Long
    Age = DateDiff("yyyy", datFrom, datTo)
    If DateAdd("yyyy", Age, datFrom) > datTo Then Age = Age - 1
End Function

[ADezii]

@NeoPa:
First and foremost, I hope that this Post finds you well. Obviously, I can see that you are still dispensing expert and professional advice. I would expect nothing else from you (LOL). As far as the matter at hand, would something more direct work, as in

Debug.Print Int(DateDiff("d",#10/12/12#,#1/7/14#)/365.25)

yields

1

[NeoPa]

Hi ADezii :-)

Right back at you sir. I'm always impressed by how you just keep on keeping on. Your body of work on here is phenomenal (I have saved links to many of your articles posted over the years). By the way - I'm an Access MVP now. Since April 2014 - Adrian Bell (NeoPa).

Getting to your question. I would say no. Cosmically speaking that's a fairly good estimate for a solar year, but human society doesn't work quite that way - at least not in the western world. I expect you'd get away with it in most cases but it's not something I'd want to rely on absolutely.

[ADezii]

By the way - I'm an Access MVP now

Congratulations , and truly well deserved. I can think of none more qualified.

[NeoPa]

Thanks ADezii. I appreciate your comment.

BTW. I wasn't the first here. That honour goes to Anders - TheSmileyCoder (Anders Ebro (TheSmileyCoder )). You've probably seen a lot of him around Access too.