Results 1 to 5 of 5
  1. #1
    Join Date
    May 2002
    Posts
    157

    Unanswered: Recording Age DateDiff with years and months

    I have the following which works quite well when only needing the age in years:
    If IsNull(Me![CAgeAtTOIR]) Then Me![CAgeAtTOIR].Value = DateDiff("yyyy", [CDateOfBirth], Now()) + Int(Format(Now(), "mmdd") < Format([CDateOfBirth], "mmdd"))

    I know the following works if I only want to record the age in months:
    If IsNull(Me![CAgeAtTOIR]) Then Me![CAgeAtTOIR].Value = DateDiff("m", [CDateOfBirth], Now()) + Int(Format(Now(), "mmdd") < Format([CDateOfBirth], "mmdd"))

    I can not work out how to get the answer in years and months, for example
    a person born on 01/01/2011 (and today's date is 6/2/2011) result in 0.01
    a person born on 01/01/2010 (and today's date is 6/2/2011) result in 1.01
    a person born on 01/02/2010 (and today's date is 6/2/2011) result in 1.00
    a person born on 01/08/2010 (and today's date is 6/2/2011) result in 0.06
    a person born on 01/03/2010 (and today's date is 6/2/2011) result in 0.11

    (results given in years and months)

    Can this be done?

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    you can probably exploit a kown quirk in MS Access / JET whic is that date/time values are stored as a number of clicks from a known dates (IIRC its first Jan 1900 or soemthign like that)

    so if yous subtract the values you have the difference in number of clicks from whatever that date is
    formatting the resultant value using the appropriate month and year values should give you want you want.

    however this is very very kludgey, not to be used if you think your db may use data storage mechanism

    the other way is to write a function which calculates the age in years and months. that is what I woudl expect is 'the' right way

    eg
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Dear Karen,

    This function should provide what you're looking for:
    Code:
    Public Function DecimalAge(ByVal DateOfBirth As Date) As Variant
    
        Dim lngMonths As Long
        Dim dteFirstDay As Date
        
        If IsNull(DateOfBirth) Then
            DecimalAge = Null
        Else
            dteFirstDay = CDate(Month(Now) & "/1/" & Year(Now))
            lngMonths = DateDiff("m", DateOfBirth, dteFirstDay)
            DecimalAge = Format(lngMonths \ 12, "0") & "." & Format(lngMonths Mod 12, "00")
        End If
            
    End Function
    If put in an independent module (i.e. not the module linked to a Form or Report nor a Class module), it can be called from everywhere in your application from another VBA procedure or it can be used in a Query.

    However, be aware that it accepts date in the American format (i.e. mm/dd/yyyy) which is the standard for Access. Should you decide that the date must always be passed in the (more common to us non-American people) Europeran format (i.e. dd/mm/yyyy). You should perform the necessary conversion when calling it or you could change the code accordingly.

    Regards.

    (P.S. I hope you're doing well).
    Have a nice day!

  4. #4
    Join Date
    May 2002
    Posts
    157
    Hi there,

    Thanks for the information. I tried and tried to get what you suggested to work but could not. In the end, I ended up with the following which does work.

    ********************************
    Dim totalmonths As Integer
    Dim monthsremaining As Integer
    Dim years As Integer

    If IsNull(Me![CAgeAtTOIR]) Then

    'Ensure that the field containing the result (eg CAgeAtTOIR) is formatted as a TEXT field within the table as the concatenation of the two variables and the "." turns the result into text.
    'Time in whole years
    years = DateDiff("yyyy", [CDateOfBirth], Now()) + Int(Format(Now(), "mmdd") < Format([CDateOfBirth], "mmdd"))
    'Time difference in whole months
    totalmonths = DateDiff("m", [CDateOfBirth], Now()) + Int(Format(Now(), "mmdd") < Format([CDateOfBirth], "mmdd"))

    monthsremaining = totalmonths - (years * 12)
    Me.CAgeAtTOIR.Value = years & "." & monthsremaining
    End If

    ******************
    thanks again for your help.

  5. #5
    Join Date
    Feb 2004
    Location
    New Zealand
    Posts
    1,424
    Provided Answers: 8
    Here some Code I use all the Time
    Code:
    Function Age(Birth_Date, End_Date)
    '***************************************
    ' Works out the age of to the month
    '
    '***************************************
    Dim Months
    Dim Years
    Dim Temp
    'Check if no error
    If IsNull(Birth_Date) Or Birth_Date = "" Or Not IsDate(Birth_Date) Then
        Age = 0#
    Else
     
        Months = DateDiff("m", CDate(Birth_Date), End_Date)
        Years = Int(Months / 12)
        Temp = Years * 12
        If Years = 0 Then Years = ""
        Age = Years & "." & Months - Temp
    
    End If
    End Function
    passing the 2 dates make so you can use in differnace ways

    want a 2 dec place

    change

    Age = Years & "." & Months - Temp
    to
    Age = Years & "." & format(Months - Temp,"00")


    to use it create a module paste the code into the module window

    in a query

    AGE:Age([dateofbirthfeild],now())

    Memberfor: age([datejoin],now())

    or

    AgeJoin: age([dateofbirthfeild],[datejoin])

    in code

    aa = Age(#1/1/1965#, Now())

    what is return is Year.month

    45.11 = 45 and 11 months
    Last edited by myle; 02-07-11 at 05:23.
    hope this help

    See clear as mud


    StePhan McKillen
    the aim is store once, not store multiple times
    Remember... Optimize 'til you die!
    Progaming environment:
    Access based on my own environment: DAO3.6/A97/A2000/A2003/A2007/A2010
    VB based on my own environment: vb6 sp5
    ASP based on my own environment: 5.6
    VB-NET based on my own environment started 2007
    SQL-2005 based on my own environment started 2008
    MYLE
    YOUR PASSWORD IS JUST LIKE YOUR TOOTHBRUSH DON'T SHARE IT.

Posting Permissions

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