Results 1 to 12 of 12
  1. #1
    3 Star Lounger
    Join Date
    Nov 2003
    Location
    London, Gtr London, England
    Posts
    222
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hi there!

    I have been struggling with putting the above on a form for someones age.

    Does anyone have a formula for this? I am ok with the years and months, but keep coming unstuck on the days

    I.e. Someone whose date of birth is 09/08/2004 equates to 5 yrs 5 mnths and 13 days, but where a date of birth is say 30/01/2005 my formula comes back with 4 yrs 11 mnths and #Error

    It's something to do with the day of the month being later than the current day.

    Any help would be grately appreciated

    Cheers,

    Niven

  2. #2
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts
    What is the formula you are using?
    Regards
    John



  3. #3
    3 Star Lounger
    Join Date
    Nov 2003
    Location
    London, Gtr London, England
    Posts
    222
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by John Hutchison View Post
    What is the formula you are using?
    It's a bit of a mess really.

    For years: - =IIf(DateValue((Left([dob],6) & Year(Date())))>Date(),DateDiff("yyyy",Left([dob],6) & Year([Dob])+1,Date()),DateDiff("yyyy",[dob],Date()))

    For Months:- =IIf(DateValue((Left([dob],6) & Year(Date())))>Date(),IIf(DatePart("d",[dob])>DatePart("d",Date()),DateDiff("m",Left([dob],6) & Year(Date())-1,Date())-1,DateDiff("m",Left([dob],6) & Year(Date())-1,Date())),DateDiff("m",Left([dob],6) & Year(Date()),Date()))

    For Days:- =IIf(Day(Date())-Day([dob])<0 And Month(Date())-Month([dob])<0,DateDiff("d",Day([dob]) & "/" & Month(Date())-1 & "/" & Year(Date()),Date()),IIf(Day(Date())-Day([dob])<0 And Month(Date())-Month([dob])>=0,DateDiff("d",Day([dob]) & "/" & Month(Date())-1 & "/" & Year(Date()),Date()),IIf(Day(Date())-Day([dob])>=0,DateDiff("d",Day([dob]) & "/" & Month(Date()) & "/" & Year(Date()),Date()))))

    The years and the months works and the days is fine if the day is before the current day. i.e. 15th before 22nd

    Cheers

    Niven

  4. #4
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts
    I thought it might be easier to stat with what you already, rather than from scratch.

    Is dob actually a Text field rather than a Date? You are using Left([dob],6) for instance.

    If it is text what format is it in?
    Regards
    John



  5. #5
    3 Star Lounger
    Join Date
    Nov 2003
    Location
    London, Gtr London, England
    Posts
    222
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by John Hutchison View Post
    I thought it might be easier to stat with what you already, rather than from scratch.

    Is dob actually a Text field rather than a Date? You are using Left([dob],6) for instance.

    If it is text what format is it in?
    It is actually a date field on the table, but being interrogated in a text field on the form.

  6. #6
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts
    Here is a sub from The Access web that does the job without using all the IIF statements seem to come up normally.



    Code:
    Public Sub CalcAge(vDate1 As Date, vdate2 As Date, ByRef vYears As Integer,
    ByRef vMonths As Integer, ByRef vDays As Integer)
    ' Comments : calculates the age in Years, Months and Days
     ' Parameters:
     ' vDate1 - D.O.B.
     ' vDate2 - Date to calculate age based on
     ' vYears - will hold the Years difference
     ' vMonths - will hold the Months difference
     ' vDays - will hold the Days difference
     vMonths = DateDiff("m", vDate1, vdate2)
     vDays = DateDiff("d", DateAdd("m", vMonths, vDate1), vdate2)
     If vDays < 0 Then
    ' wierd way that DateDiff works, fix it here
     vMonths = vMonths - 1
     vDays = DateDiff("d", DateAdd("m", vMonths, vDate1), vdate2)
     End If
     vYears = vMonths \ 12 ' integer division
     vMonths = vMonths Mod 12 ' only want leftover less than one year
    End Sub
    This function would be appropriate where you wanted to display Age on a form that showed one person at a time.

    You would need to declare three variables vYears, vMonths and vDays.

    Then in the On Current event of the form and the After Update event for DOB, run procedure passing it DOB and Date(), then set the three text boxes to the values of the three variables.
    Regards
    John



  7. #7
    3 Star Lounger
    Join Date
    Nov 2003
    Location
    London, Gtr London, England
    Posts
    222
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by John Hutchison View Post
    Here is a sub from The Access web that does the job without using all the IIF statements seem to come up normally.



    Code:
    Public Sub CalcAge(vDate1 As Date, vdate2 As Date, ByRef vYears As Integer,
    ByRef vMonths As Integer, ByRef vDays As Integer)
    ' Comments : calculates the age in Years, Months and Days
     ' Parameters:
     ' vDate1 - D.O.B.
     ' vDate2 - Date to calculate age based on
     ' vYears - will hold the Years difference
     ' vMonths - will hold the Months difference
     ' vDays - will hold the Days difference
     vMonths = DateDiff("m", vDate1, vdate2)
     vDays = DateDiff("d", DateAdd("m", vMonths, vDate1), vdate2)
    
    
     If vDays < 0 Then
    ' wierd way that DateDiff works, fix it here
     vMonths = vMonths - 1
     vDays = DateDiff("d", DateAdd("m", vMonths, vDate1), vdate2)
     End If
     vYears = vMonths \ 12 ' integer division
     vMonths = vMonths Mod 12 ' only want leftover less than one year
    End Sub
    This function would be appropriate where you wanted to display Age on a form that showed one person at a time.

    You would need to declare three variables vYears, vMonths and vDays.

    Then in the On Current event of the form and the After Update event for DOB, run procedure passing it DOB and Date(), then set the three text boxes to the values of the three variables.
    John,

    Many thanks for this. I'll try it out and let you know

    Cheers,

    Niven

  8. #8
    New Lounger
    Join Date
    Dec 2009
    Location
    Tokoroa, Waikato, New Zealand
    Posts
    1
    Thanks
    0
    Thanked 0 Times in 0 Posts
    This is an Excel spreadsheet I use for a Birthday list which does give the correct result for year, months, days.

    Perhaps a similar format or query may work in Access or not.
    Attached Files Attached Files

  9. #9
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts
    The Excel example simplifies things by only working it out for 31/12/2010. This means it can completely bypass the complicating issues:
    • Has the person had their birthday yet this year? and
    • Have they passed the day of their birthday in the current month?
    Regards
    John



  10. #10
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts
    This morning while looking for something else I came across this post by Slinky (aka Hans) that has the following expressions that could be put into a query.


    Years: Year([Date2])-Year([Date1])+(Month([Date2])<Month([Date1]) Or Month([Date2])=Month([Date1]) And Day([Date2])<Day([Date1]))

    Months: (Month([Date2])-Month([Date1])+(Day([Date2])<Day([Date1]))+12) Mod 12

    Days: DateDiff("d",DateSerial(Year([Date2]),Month([Date2])+(Day([Date2])<Day([Date1])),Day([Date1])),[Date2])
    Regards
    John



  11. #11
    3 Star Lounger
    Join Date
    Nov 2003
    Location
    London, Gtr London, England
    Posts
    222
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by Niven View Post
    John,

    Many thanks for this. I'll try it out and let you know

    Cheers,

    Niven
    John,

    Have tried this out and it works a treat!

    Many thanks for your time on this. Your later post should be quite usesfule too!

    Cheers,

    Niven

  12. #12
    3 Star Lounger
    Join Date
    Nov 2003
    Location
    London, Gtr London, England
    Posts
    222
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by John Hutchison View Post
    This morning while looking for something else I came across this post by Slinky (aka Hans) that has the following expressions that could be put into a query.


    Years: Year([Date2])-Year([Date1])+(Month([Date2])<Month([Date1]) Or Month([Date2])=Month([Date1]) And Day([Date2])<Day([Date1]))

    Months: (Month([Date2])-Month([Date1])+(Day([Date2])<Day([Date1]))+12) Mod 12

    Days: DateDiff("d",DateSerial(Year([Date2]),Month([Date2])+(Day([Date2])<Day([Date1])),Day([Date1])),[Date2])
    John,

    This works a treat as well!

    Once again, many thanks for your time on this.
    Cheers,

    Niven

Posting Permissions

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