By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,456 Members | 1,529 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,456 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
Share this Question
Share on Google+
4 Replies


gnawoncents
100+
P: 212
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
  1. (Date - DateValue("12-31-2012")) / 365.25
  2.  
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

Narender Sagar
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

gnawoncents
100+
P: 212
@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

NeoPa
Expert Mod 15k+
P: 31,186
I suspect you're wanting :
Expand|Select|Wrap|Line Numbers
  1. (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

Post your reply

Sign in to post your reply or Sign up for a free account.