Results 1 to 7 of 7
  1. #1
    Join Date
    Oct 2003
    Posts
    19

    Unanswered: How do I calculate Age?

    I need to calculate the age (Integer) of a person based on their birthday: [Birthday]

    I'm currently using:

    [Age] = Int(DateDiff("yyyy",[Birthday],Date()))

    But it's only caclulating years, and ignoring the date. Example

    If the current date is 10/20/03

    A [birthday] of 2/4/76 will return an [Age] of 27. But a date of 12/01/76 also returns 27. Only when the birthday passes the new year (01/01/77), will the number will change. Please help!

    Thanks!
    jneuwirth

  2. #2
    Join Date
    Oct 2003
    Location
    Canada
    Posts
    574
    You could check if their birthday has already gone by and add one to the age if it has:

    if int(format("mmdd",Date)) > int(format("mmdd",Birthday)) then
    age = age + 1
    end if

    or subtract 1 if need be

  3. #3
    Join Date
    Oct 2003
    Posts
    19
    Thanks! jmrSudbury

    You know I was thinking the same concept, but I didn't know the exact syntax... I'll try that and let you know

    Thanks again.

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    You could check if their birthday has already gone by...
    the following will do just that

    the age goes up on the birthday, and not a day sooner
    Code:
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Oct 2003
    Posts
    19
    I appologise for my newness to database design, but I'm not quite sure how to use that code or where to put it. Can that be put into the SQL Code for a query? I'm only familiar with using calculations (=..etc.) and queries in access. I'd love to learn how though!

    I actually used jmrSudbury's idea and came up with the following formula entered into a text box on my form and it works! It looks very much like what you suggested:

    =IIf(Format(Date(),"mmdd")
    >=Format([Birthday],"mmdd"),
    Int(DateDiff("yyyy",[Birthday],Date())),
    Int(DateDiff("yyyy",[Birthday],Date()))-1)

    Thank you for your help, r937.

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    i'm sorry, i don't do access forms

    yes, you would paste my query in the SQL window when you create a query, go to Design view, and select View > SQL view

    make sure you change the table name to yours


    rudy

  7. #7
    Join Date
    Oct 2003
    Posts
    19
    Awsome. I think I'm going to start learning to write code so I can really know what I'm doing!

    Thanks so much for your help, rudy.

    Joey.

Posting Permissions

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