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,482
    Provided Answers: 11
    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

    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-NET based on my own environment started 2007
    SQL-2005 based on my own environment started 2008
    YOUR PASSWORD IS JUST LIKE YOUR TOOTHBRUSH DON'T SHARE IT.
    DONT WORRY ABOUT THOSE WHO TALK BEHIND YOUR BACK
    THEY'RE BEHIND YOU FOR A REASON

  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,482
    Provided Answers: 11
    I didn't adapt Your Idea HTH

    But I do agree with his IIF if can get messy inside a iif()
    hope this help

    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-NET based on my own environment started 2007
    SQL-2005 based on my own environment started 2008
    YOUR PASSWORD IS JUST LIKE YOUR TOOTHBRUSH DON'T SHARE IT.
    DONT WORRY ABOUT THOSE WHO TALK BEHIND YOUR BACK
    THEY'RE BEHIND YOU FOR A REASON

  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
  •