Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

CALCULATING AGE AT A SPECIFIED DATE 2

Status
Not open for further replies.

w0wsmg

Technical User
Jan 22, 2012
2
US
I have a database where I need to calculate the members age (in years and months) as of a specific date (6/1/2018 )
I am using BDATE and SDATE as my fieldnames

Example - MEMBER with BDATE of 7/1/2010 would return a value of 8.1
MEMBER with BDATE OF 5/1/2010 would return a value of 7.11

Thanks in advance for your help.
 
Example - MEMBER with BDATE of 7/1/2010 would return a value of 8.1
MEMBER with BDATE OF 5/1/2010 would return a value of 7.11
I think you mean
Example - MEMBER with BDATE of 7/1/2010 would return a value of 7.11
MEMBER with BDATE OF 5/1/2010 would return a value of 8.1

Years.Months is a strange value to show. I think you would be better off without the decimal. Kind of confusing. The below function can be called from a query. It accounts for full months and corrects for leap years.
"Select YearsDecimalMonths([bdate],[sdate]) as Age, otherFields, from someTable"
Code:
Public Function YearsDecimalMonths(ByVal StartDate As Date, ByVal EndDate As Date) As Variant
  Dim intDiff   As Integer
  Dim intMonths As Integer
  Dim intSign As Integer
  ' Find difference in calendar months.
  intMonths = DateDiff("m", StartDate, EndDate)
  ' Check if the second date falls before, on, or after the crossing date for a 1 month period
  ' while at the same time correcting for February 29. of leap years.
  intSign = Sgn(DateDiff("d", DateAdd("m", intMonths, StartDate), EndDate))
  intDiff = Abs(intSign < 0)
   ' Return count of months as count of full 1 month periods.
  Months = intMonths - intDiff
  YearsDecimalMonths = Months \ 12 & "." & Months Mod 12
  ' YearsDecimalMonths = Months \ 12 & " Years " & Months Mod 12 & " Months"
End Function
 
Thank you MajP - this works perfectly!!!
 
w0wsmg,

It's appropriate to click the "Treat post!" link to indicate the thread has been resolved and it gives some kudos to MajP.

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top