Setting Code to Simple Numeric Age

[wes9659]

Out of curiosity just for learning if I wanted this particular code which works perfectly to generate Age simple in years i.e. 56. What changes would I need to change? This is a public function I currently use. I simply want it to give me a numeric age only. Rather than this which current code generates the following example: 93 years, 3 months and 30 days. I want to compare and contrast both and see what is different I learn easier that way. Thanks everyone for your help. Public Function FindAge(DOB As Date, CalcDate As Date) As String Dim intYears As Integer, intMonths As Integer, intDays As Integer intMonths = DateDiff("m", DOB, CalcDate) intDays = DateDiff("d", DateAdd("m", intMonths, DOB), CalcDate) If intDays < 0 Then intMonths = intMonths - 1 intDays = DateDiff("d", DateAdd("m", intMonths, DOB), CalcDate) End If intYears = intMonths \ 12 intMonths = intMonths Mod 12 FindAge = intYears & " year" & IIf(intYears = 1, "", "s") _ & ", " & intMonths & " month" & IIf(intMonths = 1, "", "s") _ & " and " & intDays & " day" & IIf(intDays = 1, "", "s") End Function

[June7]

Don't really need VBA. An expression in query or textbox can return numeric age.

How much precision do you want?

DateDiff("yyyy", [datefield], Now())

will return 0 years for an infant of less than 1 year old but will return 1 year for a toddler of 18 months.


More about DateDiff http://office.microsoft.com/en-us/ac...001228811.aspx

[ssanfu]

Could be as simple as

Public Function FindAge(DOB As Date, CalcDate As Date) As String
    Dim intYears As Integer

    intYears = DateDiff("yyyy", DOB, CalcDate) 
    FindAge = intYears & " year" & IIf(intYears = 1, "", "s") _

End Function

But if the birth date is Nov and the current month is Aug, the person will be reported 1 year older.

I use

Function GetAge(varDOB As Variant, Optional varAsOf As Variant) As Variant
   'Purpose:   Return the Age in years.
   'Arguments: varDOB = Date Of Birth
   '           varAsOf = the date to calculate the age at, or today if missing.
   'Return:    Whole number of years.
   Dim dtDOB As Date
   Dim dtAsOf As Date
   Dim dtBDay As Date  'Birthday in the year of calculation.

   GetAge = Null          'Initialize to Null

   'Validate parameters
   If IsDate(varDOB) Then
      dtDOB = varDOB

      If Not IsDate(varAsOf) Then  'Date to calculate age from.
         dtAsOf = Date
      Else
         dtAsOf = varAsOf
      End If

      If dtAsOf >= dtDOB Then      'Calculate only if it's after person was born.
         dtBDay = DateSerial(Year(dtAsOf), Month(dtDOB), Day(dtDOB))
         GetAge = DateDiff("yyyy", dtDOB, dtAsOf) + (dtBDay > dtAsOf)
      End If
   End If
End Function

[wes9659]

Code works beautifully I do see how by making those changes can alter the output. Hope everyone has a great day. Once again thank you for teaching me Access and VBA coding.

[azhar2006]

Thank you ssanfu
How to Add Function GetAge text box on the form
Thanks in advance

[June7]

Call function in textbox ControlSource.

=GetAge([DOB field])

[ssanfu]

Like June said.

But your example had an additional parameter (as does mine). So use:

=Getage(DOB, CalcDate)