Calculate age in years and months

[MrsRum]

Hello all - I know this question has been asked 1000 times, BUT.....I
need to calculate age at death in years and months. (for example: 55
years 3 months.)

The kicker is that I am NOT a programmer. I don't understand VBA or
SQL or any of those. I work solely in design view when designing
queries.

I have followed all the links, I have read all the suggestions. I
cannot make them work because I don't understand them.

Surely there is a way to design this in design view? Something?
Anything? I desperately need this field to work.

Any help would be greatly, greatly, appreciated

Mrs Rum

 

[John Spencer]

First, how do you want to calculate months? Should a partial month
count as a whole month or do we count only whole months?

The following expression will give you partial months as whole months
and return a string that read 55 years 3 months.

DateDiff("m",[Date Of Birth],[Date of Death]) \12 & " years and " &
DateDiff("m",[Date Of Birth],[Date of Death]) Mod 12 & " months"

There is a possible problem in that you could get 55 years and 0 Months
returned when the person dies in their birth month. Also you will see 1
months instead of 1 month. Also if the person is less than a year old
you will see 0 Years and 9 months as a possible result. If those are
problems for you, you will need a more complex expression.

Try the "More Complete DateDiff Function" Graham Seach and Doug Steele
wrote.

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

You specify how you want the difference between two date/times to be
calculated by providing which of ymdhns (for years, months, days, hours,
minutes and seconds) you want calculated.

For example:

?Diff2Dates("y", #06/01/1998#, #06/26/2002#)
4 years
?Diff2Dates("ymd", #06/01/1998#, #06/26/2002#)
4 years 25 days
?Diff2Dates("ymd", #06/01/1998#, #06/26/2002#, True)
4 years 0 months 25 days
?Diff2Dates("d", #06/01/1998#, #06/26/2002#)
1486 days

?Diff2Dates("h", #01/25/2002 01:23:01#, #01/26/2002 20:10:34#)
42 hours
?Diff2Dates("hns", #01/25/2002 01:23:01#, #01/26/2002 20:10:34#)
42 hours 47 minutes 33 seconds
?Diff2Dates("dhns", #01/25/2002 01:23:01#, #01/26/2002 20:10:34#)
1 day 18 hours 47 minutes 33 seconds

?Diff2Dates("ymd",#12/31/1999#,#1/1/2000#)
1 day
?Diff2Dates("ymd",#1/1/2000#,#12/31/1999#)
-1 day
?Diff2Dates("ymd",#1/1/2000#,#1/2/2000#)
1 day

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
'====================================================


DateDiff("m",[Date Of Birth],[Date of Death]) \12 & " years and " &
DateDiff("m",[Date Of Birth],[Date of Death]) Mod 12 & " months"

 

[Roger Carlson]

Assuming you have two fields: DOB and DOD (date of birth, date of death),
you can write an expression like this:

Age At Death: DateDiff("yyyy",[dob],[dod]) & " yr. - " &
DateDiff("m",[dob],[dod])-(DateDiff("yyyy",[dob],[dod])*12) & " mo."


--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

 

[MrsRum]

First, how do you want to calculate months?  Should a partial month
count as a whole month or do we count only whole months?

The following expression will give you partial months as whole months
and return a string that read 55 years 3 months.

DateDiff("m",[Date Of Birth],[Date of Death]) \12 & " years and " &
DateDiff("m",[Date Of Birth],[Date of Death]) Mod 12 & " months"

There is a possible problem in that you could get 55 years and 0 Months
returned when the person dies in their birth month. Also you will see 1
months instead of 1 month. Also if the person is less than a year old
you will see 0 Years and 9 months as a possible result.  If those are
problems for you, you will need a more complex expression.

Try the "More Complete DateDiff Function" Graham Seach and Doug Steele
wrote.

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

You specify how you want the difference between two date/times to be
calculated by providing which of ymdhns (for years, months, days, hours,
minutes and seconds) you want calculated.

   For example:

     ?Diff2Dates("y", #06/01/1998#, #06/26/2002#)
     4 years
     ?Diff2Dates("ymd", #06/01/1998#, #06/26/2002#)
     4 years 25 days
     ?Diff2Dates("ymd", #06/01/1998#, #06/26/2002#, True)
     4 years 0 months 25 days
     ?Diff2Dates("d", #06/01/1998#, #06/26/2002#)
     1486 days

     ?Diff2Dates("h", #01/25/2002 01:23:01#, #01/26/2002 20:10:34#)
     42 hours
     ?Diff2Dates("hns", #01/25/2002 01:23:01#, #01/26/2002 20:10:34#)
     42 hours 47 minutes 33 seconds
     ?Diff2Dates("dhns", #01/25/2002 01:23:01#, #01/26/2002 20:10:34#)
     1 day 18 hours 47 minutes 33 seconds

     ?Diff2Dates("ymd",#12/31/1999#,#1/1/2000#)
     1 day
     ?Diff2Dates("ymd",#1/1/2000#,#12/31/1999#)
     -1 day
     ?Diff2Dates("ymd",#1/1/2000#,#1/2/2000#)
     1 day

'====================================================
  John Spencer
  Access MVP 2002-2005, 2007-2008
  The Hilltop Institute
  University of Maryland Baltimore County
'====================================================

DateDiff("m",[Date Of Birth],[Date of Death]) \12 & " years and " &
DateDiff("m",[Date Of Birth],[Date of Death]) Mod 12 & " months"

Hello all - I know this question has been asked 1000 times, BUT.....I
need to calculate age at death in years and months.  (for example: 55
years 3 months.)

The kicker is that I am NOT a programmer.  I don't understand VBA or
SQL or any of those.  I work solely in design view when designing
queries.

I have followed all the links, I have read all the suggestions.  I
cannot make them work because I don't understand them.

Surely there is a way to design this in design view?  Something?
Anything?  I desperately need this field to work.

Any help would be greatly, greatly, appreciated

Mrs Rum- Hide quoted text -

- Show quoted text -

Thank you both! I didn't need to be super exact and an entry of
"Years 9 months" while depressing in calculating age at death is
perfectly acceptable for my needs. It seems that your solutions
worked just fine.

I apologize for my delay in thanking you - I just wanted to be sure
that it worked. Thanks again and thanks for responding so quickly!

Mrs Rum