Results 1 to 4 of 4
  1. #1
    ricks1675mba is offline Novice
    Windows 7 64bit Access 2010 32bit
    Join Date
    Sep 2014
    Posts
    1

    Need to calculate age that includes years, months and days


    I am creating an Access Database for my Church that will calculate the age of the person at certain milestones, example: Age at First Communion. I have been able to calculate their ages using "([First Communion Date]-[Birthdate])/365.25", however this creates an age xx.xx. I am looking to have it formatted as Years, Months and Days. I am self taught in Access and I don't know much about how to go about doing this and I don't know Visual Basic at all. Is there an easy way to do this?

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    Probably not easy because months do not have the same number of days. And then there are leap years.

    Bing: Access vba age years month days
    Maybe this has example:
    https://www.accessforums.net/program...ays-21024.html
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    qa12dx is offline Competent Performer
    Windows 7 64bit Access 2007
    Join Date
    Nov 2012
    Location
    india
    Posts
    112
    date-time is often a very abmigous field to work with. calculating age very accurately is sometimes more of a hassle than its worth.

    for your q, how do we solve this?
    say a person is born in 4/nov/1999. how old is that person today, 22/sep/2014.
    1999 to 2014, 15 years. but dob is nov>sep so 14 yrs. easy enough.

    Month LOGIC - how many months? since nov>sep, 12-11+9=10. if dob month was less, say feb, then simpler sep-feb = 9-2 = 7

    Day LOGIC - how many days? same way as month. 4<22 so 22-4 = 18. if days was 26 then [30 days in a month] - [26 dob day] + [22 date today]=26

    so the answer would be 14 yrs, 10 months, 26 days. which is maybe a coulpe of days here there. number of days vary per month. above we took 30 as default days in month, 30*12=360, 5 days?

    that's why to be very accurate is sometimes more of a hassle than its worth.

    so how do we convert this to access?
    2 things you need to know,
    extracting vales from a date,
    expr1 - dob=4/nov/1999 -> format([dob],"yyyy") will give u 1999
    expr2 -dob=4/nov/1999 -> format([dob],"m") will give u 11
    expr3 -dob=4/nov/1999 -> format([dob],"d") will give u 4

    and datediff function
    date1=dob and date2 = todays date, in access to get todays date u can use Date(), for time now, Time()

    expr4 - DateDiff("d",[dob],Date()) = 5436
    that is number of days between these 2 dates

    also int function. any number u want to force round of
    int(22/7)=3 no decimals

    years=int([expr4]/356)= 14
    month= u know how to extract months from date. now use month logic
    day = day logic

    whooeee

  4. #4
    tonez90 is offline Novice
    Windows 7 64bit Access 2010 32bit
    Join Date
    Jul 2014
    Location
    Australia
    Posts
    18
    You could use something like this:
    Public Function fAgeYMD(StartDate As Date, EndDate As Date) As String
    'Purpose: Returns the difference between StartDate and EndDate in full years, months and days
    'To call:
    ' ? fAgeYMD(#7/6/54#, #10/3/84#)
    'Returns:
    ' 30 years 2 months 28 days
    Dim inthold As Integer, dayHold As Integer
    inthold = Int(DateDiff("m", StartDate, EndDate)) + (EndDate < DateSerial(Year(EndDate), Month(EndDate), Day(StartDate)))

    If Day(EndDate) < Day(StartDate) Then
    dayHold = DateDiff("d", StartDate, DateSerial(Year(StartDate), Month(StartDate) + 1, 0)) & Day(EndDate)
    Else
    dayHold = Day(EndDate) - Day(StartDate)
    End If

    fAgeYMD = Int(inthold / 12) & " year" & IIf(Int(inthold / 12) <> 1, "s ", " ") _
    & inthold Mod 12 & " month" & IIf(inthold Mod 12 <> 1, "s ", " ") _
    & LTrim(str(dayHold)) & " day" & IIf(dayHold <> 1, "s", "")
    End Function


    You could set a date variable to contain today's date and then take the birthday from your records (REMEMBER the hashes before and at end of date)

    Heres a second way (by Vybercow)
    Public Function myAge(dteDate As Date) As String
    ' Written by Cybercow - modified by dancingwaves (Becca)
    Dim intYears As Integer
    Dim intMonths As Integer
    Dim intDays As Integer
    'Make sure the date provided is past date
    If dteDate <= Date Then
    'Determine the number of years between the date provided and the current date
    intYears = DateDiff("yyyy", dteDate, Date)
    'Determine the number of months between the date provided and the current date using the current year
    'as part of the from date to only return months
    intMonths = DateDiff("m", DateSerial(Year(Date), Month(dteDate), Day(dteDate)), Date)
    MonthsCalc:
    'If the months returned are less then 0, subtract 1 from the year calculation and redo the months calculation
    'subtracting one from the current year
    If intMonths < 0 Then
    intYears = intYears - 1
    intMonths = DateDiff("m", DateSerial(Year(Date) - 1, Month(dteDate), Day(dteDate)), Date)
    End If
    'Determine the number of days between the date provided and the current date using the current year
    'and current month as part of the from date to only return days
    intDays = DateDiff("d", DateSerial(Year(Date), Month(Date), Day(dteDate)), Date)
    'if the days returned are less then 0, subtract 1 from the month calculation and redo the days calculation
    'subtracting one from the current month.
    If intDays < 0 Then
    intMonths = intMonths - 1
    'test to see if resulting intMonths is <0. If so, recalculate months.
    If intMonths < 0 Then
    GoTo MonthsCalc
    End If
    intDays = DateDiff("d", DateSerial(Year(Date), Month(Date) - 1, Day(dteDate)), Date)
    End If
    'Assemble the return string
    myAge = intYears & " Years, " & intMonths & " Months, " & intDays & " Days."
    Else
    'If the date provided is in the future, return Negative Age
    myAge = "Negative Age"
    End If
    End Function

    and here is the simplest
    Public Function CalculateAge(DOB As Date) As Integer
    Dim WorkDate As Date
    Dim RawAge As Integer
    RawAge = DateDiff("yyyy", DOB, Date)
    WorkDate = DateSerial(Year(Date), Month(DOB), Day(DOB))
    CalculateAge = RawAge + (Date < WorkDate) '(Date < WorkDate) = 0 or -1
    End Function


    regards
    Tonez90

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 12
    Last Post: 08-10-2014, 12:54 PM
  2. Replies: 1
    Last Post: 07-10-2012, 06:23 AM
  3. Display Age in Years, Months, Days
    By jsimard in forum Programming
    Replies: 1
    Last Post: 01-18-2012, 08:08 PM
  4. Adding months to years in queries
    By TonyB in forum Queries
    Replies: 2
    Last Post: 07-29-2011, 09:29 AM
  5. Replies: 1
    Last Post: 11-12-2010, 01:16 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums