Results 1 to 8 of 8

Thread: DOB question

  1. #1
    Join Date
    Sep 2004
    Posts
    29

    Unanswered: DOB question

    Hi all,

    I am trying to evaluate a persons age in the month of june of each year. For example I need to know how old was a person in June 2005 and how old this person is going to be in June 2006.

    I have managed to do this query, but there is some hardcoding in there. I need this query to be dynamic and based on DATE() function.

    SELECT DDN.DOB, Year(Date())-Year(dob)-IIf(Month(Date())>Month(dob),0,IIf(Month(Date())<M onth(dob),1,IIf(Day(Date())<Day(dob),1,0))) AS AgeActuel, Year(#6/30/2005#)-Year(dob)-IIf(Month(#6/30/2005#)>Month(dob),0,IIf(Month(#6/30/2005#)<Month(dob),1,IIf(Day(#6/30/2005#)<Day(dob),1,0))) AS Juin2005, Year(#6/30/2006#)-Year(dob)-IIf(Month(#6/30/2006#)>Month(dob),0,IIf(Month(#6/30/2006#)<Month(dob),1,IIf(Day(#6/30/2006#)<Day(dob),1,0))) AS Juin2006
    FROM DDN;


    Thanks

    RC

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Hi

    I don't piddle about with SQL for this. It makes for ugly, difficult to follow code. It is also easy to make a mistake. Use VB (e.g. http://support.microsoft.com/?scid=k...d=2509&sid=202) instead and your SQL becomes dead easy

    HTH
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    BTW - if you know how old someone will be in June2005... does that not leave with you with the answer for 2006?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  4. #4
    Join Date
    Sep 2004
    Posts
    29
    Thanks for your input...the function is nice.

    I still need to know how old a person is on each end of june. The reason behind this is to know if a competitor for a sports meet matches the age criteria on june end of each year

    So for example, if today is Jan 15 2006, i need to know how old a competitor was on end june 2005. If we are say July 15 2006, i need to know how hold is the same competitor on end june 2006.

    I know this can be done with IIF in SQL...I have hardcoded the date values there but it's not practical. I could also store the limit dates values in the database but it's not what i'd want to do.

    Cheers

    RC

  5. #5
    Join Date
    Feb 2004
    Location
    New Zealand
    Posts
    1,420
    Provided Answers: 7
    Here is a Function I wrote

    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 ="" Then
        Age=0.0 
    else
     
        Months = DateDiff("m", CDate(Birth_Date), End_Date)
        Years = Int(Months / 12)
        Temp = Years * 12
        Age =  Years & "." & (Months - Temp)
    End if
    
    End Function
    Paste into a Module

    then in a query

    Bla : age([startdate],[enddate])


    if you want age now

    Bla : age([startdate],date())

    and

    Bla : age([startdate],#01/06/2007#)


    I use in the payroll program I wrote

    how long been working here for end of fin year
    Last edited by myle; 06-30-06 at 01:22.
    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.

  6. #6
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by duaner
    So for example, if today is Jan 15 2006, i need to know how old a competitor was on end june 2005. If we are say July 15 2006, i need to know how hold is the same competitor on end june 2006.
    Hi RC

    But that is my point - whether you are looking on Jan 15 2006 or July 15 2006 the competitor will be the same age in June 2006 - one year older than in June 2005. Each and every time.

    You can do it in an Iif but I would seriously avoid. myle has what is effectively an adaption of the function I posted with an additional "on this date" criteria. Call this for #01/06/2005# and again for #01/06/2006#

    HTH
    Testimonial:
    pootle flump
    ur codings are working excelent.

  7. #7
    Join Date
    Feb 2004
    Location
    New Zealand
    Posts
    1,420
    Provided Answers: 7
    I didn't adapt Your Idea HTH

    But I do agree with his IIF if can get messy inside a iif()
    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.

  8. #8
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Hi Myle

    HTH = Hope This Helps

    I know you didn't adapt it. I put in what is effectively because it essentially does the same as the function I linked to except that you've added the ability to provide the date for which you want the "age" rather than always using today. I was trying to stress that these were (to all intents and purposes) the same (rather than competing) solutions just that the code differed a little and you had added the parameter duaner would need.
    Testimonial:
    pootle flump
    ur codings are working excelent.

Posting Permissions

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