435,121 Members | 1,706 Online
Need help? Post your question and get tips & solutions from a community of 435,121 IT Pros & Developers. It's quick & easy.

# Calculating years between dates

 P: 1 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 Feb 21 '18 #1
4 Replies

 100+ P: 214 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. Expand|Select|Wrap|Line Numbers (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/answe...listbox-report Feb 22 '18 #2

 100+ P: 189 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? Feb 22 '18 #3

 100+ P: 214 @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. Feb 22 '18 #4

 Expert Mod 15k+ P: 31,494 I suspect you're wanting : Expand|Select|Wrap|Line Numbers (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. Feb 22 '18 #5