CALCULATING AGE AT A SPECIFIED DATE 2

[w0wsmg]

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.

 

[MajP]

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"

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

 

[w0wsmg]

Thank you MajP - this works perfectly!!!

 

[dhookom]

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