Results 1 to 5 of 5
  1. #1
    Join Date
    Apr 2006
    Posts
    15

    Unanswered: Help Needed For Auto Calculation Of Age

    hi guys.

    i got a problem. i want to calculate the excate age of all my members and displayed it on the textbox on the main form.

    Below is the codes i keyed in in the property window of the textbox under control source. But it only check for the year. I want to check for the month as well as the day.... how can i go abt doing it?

    =DateDiff("yyyy",[Date Of Birth],Date())

    Thanks a million in advance

  2. #2
    Join Date
    Oct 2003
    Location
    Ger
    Posts
    1,969
    Provided Answers: 1
    If you have already stored all the birthdays in your table you can compute the exact age using this SELECT statement

    SELECT birthday
    , Year(Date()) - Year(birthday)
    - iif(Month(Date()) > Month(birthday),0,
    iif(Month(Date()) < Month(birthday),1,
    iif(Day(Date()) < Day(birthday),1,0))) as age
    from yourTable

  3. #3
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    equally you could use the following forumula in a function to calculat ethe age

    function CalcAge(dtDoB as date) as string
    Dim dtAge As Date
    dtAge = Now() - dtDoB 'find the differnce betweent he date of birth and the current date/time
    CalcAge = Year(dtAge) - 1900 & " years," & Month(dtAge) - 1 & " months and " & Day(dtAge) & " days"
    'can change this for wnythign preferred
    'could return "just the age ie xx years and numeric if required
    'could return the full date time difference (ie if you knew the precise time born then could also return the hours minutes aswell as the age.
    end function

    no reason why your couldn't abstract the fucntion with a format type
    enum AgeStyle
    1= yearsonly
    2=YearsandMonths
    3 = yearsmonthsanddays
    ...etc
    end enum
    eg function CalcAge(dtDoB as date,intStyle as agestyle) as variant


    depends on what you want to do....
    I'd rather be riding on the Tiger 800 or the Norton

  4. #4
    Join Date
    Apr 2006
    Location
    Huddersfield, UK
    Posts
    154
    also try, in an unbound text box on either forms or reports

    =DateDiff("yyyy",[DOB],Date())+(Date()<DateSerial(Year(Date()),Month([DOB]),Day([DOB])))

    just copy and paste this code, you may need to change the sections highlighted if your date of birth field is called someting other than DOB (such as birthday)

    Dan
    sometimes simple is best.... and i'm just a simple fellow.

  5. #5
    Join Date
    Apr 2006
    Posts
    15
    thanks guys for all ur reply...

    i tried
    =DateDiff("yyyy",[DOB],Date())+(Date()<DateSerial(Year(Date()),Month([DOB]),Day([DOB])))

    it works well...

Posting Permissions

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