Results 1 to 3 of 3
  1. #1
    Join Date
    Feb 2012
    Location
    Hiawassee, GA
    Posts
    1

    Unanswered: Calculating Years and Months from DOB to Now

    I am very new to MS Access and want a field titled "Age" to show the years and Months from a previous field titled "DOB". In the "DOB" field I enter the date of birth of an animal entering our rescue group.

    I need to know what to enter and where to enter it. The more details the better.

    Thanks Billie

  2. #2
    Join Date
    Feb 2012
    Posts
    13

    Use a query

    Design a query and use the keywords given in the link below

    Use current date and time in calculations - Access - Office.com

    U can enter the the condition, (Date()-DOB) in the "Criteria" field while designing the query

  3. #3
    Join Date
    Mar 2009
    Posts
    5,441
    Provided Answers: 14
    1. Create a new module or open an existing one (it must be an independent module, i.e. not the module of a form or report) and paste the following code into it:
    Code:
    Public Function Age(ByVal DoB As Date, Optional ByVal Separator As String = "-")
    
        If IsDate(DoB) Then Age = DateDiff("m", DoB, Date) \ 12 & Separator & DateDiff("m", DoB, Date) Mod 12
    
        
    End Function
    2. To use the function in a query:
    Code:
    SELECT MyTable.FirstName, 
           MyTable.LastName, 
           MyTable.DoB, 
           Age(MyTable.DoB) AS Age
      FROM MyTable;
    3. To use the function in a VBA expression:
    Code:
    Sub TestAge()
    
        Dim dteDateOfBirth As Date
        Dim strAge As String
        
        dteDateOfBirth = DLookup("DoB", "MyTable", "FirstName='John' AND LastName='Doe'")
        strAge = Age(dteDateOfBirth)
        
    End Sub
    4. To use it in the ControlSource property of a TextBox control on a form:
    a) In the Property wondows: =age([DoB])
    b) Dynamically:
    Code:
    Private Sub Form_Open(Cancel As Integer)
    
        Me.Text_Age.ControlSource = "=Age([DoB])"
        
    End Sub
    5. To dynamically recompute it in a form:
    Code:
    Private Sub Form_Current()
    
        Text_DoB_AfterUpdate
    
    End Sub
    
    Private Sub Text_DoB_AfterUpdate()
    
        Me.Text_Age.value = Age(Me.Text_DoB.value)
    
    End Sub
    Note: By default the function returns the age in the format: "Year-Month". You can change the separator between the Year part and the Month part from its default (hyphen "-") to anything else by passing the optional second parameter (Separator) to the function:
    Code:
    Debug.Print age(#01/23/1976#) ' Yields: 36-2
    Debug.Print age(#01/23/1976#, ".") ' Yields: 36.2
    If you want to retrieve both parts (Year and Month) seperately, you can use:
    Code:
    Sub TestAgeSplit()
    
        Dim dteDateOfBirth As Date
        Dim varAge As Variant
        
        dteDateOfBirth = #01/23/1976#
        varAge = Split(Age(dteDateOfBirth), "-")
        Debug.Print varAge(0) ' Yields: 36
        Debug.Print varAge(1) ' Yields: 2
        
    End Sub
    Have a nice day!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •