Calculating years between dates

[Stephcar]

In an update query I need to calculate the number of years between 12/31/2012 and various years. The integer function is to be used with the calculation. I get ridiculous negative answers.
Example. 12/31/2012- 1/5/2008
I am using (12/31/2012-[date])/365.25
Any help would be appreciated

[gnawoncents]

Welcome to Bytes, Stephcar!
Writing your code that way is actually giving you 12 divided by 31 divided by 2012 minus CurrentDate divided by 365.25. That is why it's returning a negative value. if 12/31/2012 is a fixed date, you're probably looking for something like the code below.

(Date - DateValue("12-31-2012")) / 365.25

If it is not a set date and can change, check out NeoPa's caution on setting up dates correctly at the thread link below.

https://bytes.com/topic/access/answers/970223-include-all-data-listbox-report

[Narender Sagar]

I think, your question is not properly explained. Why are you dividing [date] with 365.25? Can you please explain your objective of dividing this?

[gnawoncents]

@Narender, as I read it, Stephcar wants the number of years between the current date and some past date. Dividing by 365.25 is one way of getting the difference converted into years.

[NeoPa]

I suspect you're wanting :

(Date - DateValue("2012-12-31")) / 365.25

yyyy-m-d is a standard, and non country specific, version of a date string recognised by SQL as well as VBA. The linked question is all about SQL. This one is also about SQL as the question's about an UPDATE query, but it works for VBA too.

NB. Using an UPDATE query is generally not recommended. Such a result would be better as a calculated value in most circumstances.