Results 1 to 11 of 11
  1. #1
    MediaCo is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jul 2011
    Posts
    6

    Calculate age based on birthday


    Hi,

    Hi, I have access 2010 and I have built a database to manage actors with fields based on their appearance:

    Here is a screenshot (http://i.imgur.com/27E6h.png)

    Now, what I am trying to do next is to make access calculate the persons current age based on their birthday. Reason for wanting such a thing will be that I want to see really quickly what the age of the actor currently is.

    Anyone has a clue on how to do this?
    - Thanks in advance.

  2. #2
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 7 64bit Access 2010 32bit
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    Here's what I use:
    DateDiff("yyyy", [DateofBirth], Date())+ Int( Format(Date(), "mmdd") < Format( [DateofBirth], "mmdd") )

  3. #3
    MediaCo is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jul 2011
    Posts
    6
    Quote Originally Posted by RuralGuy View Post
    Here's what I use:
    DateDiff("yyyy", [DateofBirth], Date())+ Int( Format(Date(), "mmdd") < Format( [DateofBirth], "mmdd") )
    Sorry, I'm an access noob :P
    Can you please give me the steps.
    Thank you

  4. #4
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 7 64bit Access 2010 32bit
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    Do you have a Date Field with their Birthday in it?

  5. #5
    MediaCo is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jul 2011
    Posts
    6
    Quote Originally Posted by RuralGuy View Post
    Do you have a Date Field with their Birthday in it?
    Yes, I do

  6. #6
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    If you only care about the number of years (not portions of a year) I use

    DateDiff("yyyy",[date1],[date2])

  7. #7
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    Let's assume in your form is called DOB

    Create another field on your form call that field PERSONAGE

    In the CONTROL SOURCE of the PERSONAGE field put in the formula

    =Datediff("yyyy", [dob], now())

    It will give you their age in years as of the moment you open up their record.

  8. #8
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 7 64bit Access 2010 32bit
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    You would set the ControlSource of the "Age" control to:
    =DateDiff("yyyy", [DateofBirth], Date())+ Int( Format(Date(), "mmdd") < Format( [DateofBirth], "mmdd") )
    ...replacing DateofBirth with the name of your DOB field or control. Everything else is exactly as written.

  9. #9
    RayMilhon is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    Aug 2011
    Location
    Southern California
    Posts
    1,067
    I have to go with RuralGuy on this one. the DateDiff("yyyy",[DateofBirth],Date()) doesn't really work as the result is the same whether the current Date is Jan 1 of this year or Dec 31 of this year. for example date of birth = 11/27/1951 the result is 60 as long as Date is anywhere in 2011.

  10. #10
    CharlotteCrowder is offline Novice
    Windows 8 Access 2010 64bit
    Join Date
    Oct 2014
    Posts
    1

    Cool

    Quote Originally Posted by MediaCo View Post
    Sorry, I'm an access noob :P
    Can you please give me the steps.
    Thank you
    This is the correct formula. Thank you, you are awesome.

    I can give you the steps.

    of course you must have a field to enter the birthday and that field must be in date format
    Then you must have a field for the age to be entered in. Access will fill this field out automatically once you put in this code provided here.
    In the design view of your form in the field's properties enter this code in the control source. You can expand by clicking on the dots so you can see better. Copy this code. You must change the field to match the exact spelling of your birthday field and it is case sensitive. There are two places in the code to make match your birthday field.

    Then when you run your form and enter the birthdate, it should update as soon as you click out of the field. I hope this helps. If this is greek to you, don't worry about calculating automatic info until you have basics down. Just know it will do it and you can figure that out later.

    Thanks again for posting this code. I have been trying for some time to figure out the syntax for this. You are my hero.

  11. #11
    ssanfu is offline Master of Nothing
    Windows XP Access 2000
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Quote Originally Posted by CharlotteCrowder View Post
    <snip>
    of course you must have a field to enter the birthday and that field must be in date format
    Then you must have a field for the age to be entered in. Access will fill this field out automatically once you put in this code provided here.
    In the design view of your form in the field's properties enter this code in the control source. You can expand by clicking on the dots so you can see better. Copy this code. You must change the field to match the exact spelling of your birthday field and it is case sensitive. There are two places in the code to make match your birthday field.
    <snip>
    Not to be too nit-picky...... but.....
    Forms
    have Controls - Controls have a control source.
    Tables
    have Fields - fields have field names.

    Since age can (and should be) calculated at any time, the age should not be stored in a table.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 1
    Last Post: 03-22-2011, 07:01 PM
  2. Replies: 5
    Last Post: 03-06-2011, 10:38 AM
  3. Birthday format
    By endri81 in forum Forms
    Replies: 1
    Last Post: 02-12-2011, 04:07 PM
  4. Calculate one field based on another one
    By Douglasrac in forum Forms
    Replies: 7
    Last Post: 11-18-2010, 01:30 PM
  5. Replies: 0
    Last Post: 02-15-2009, 09:14 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums