Results 1 to 10 of 10
  1. #1
    oldlearner is offline Advanced Beginner
    Windows 8 Access 2013
    Join Date
    Sep 2015
    Posts
    30

    Calculate age from date of birth in a table

    Hi There, can anybody give me the correct expression for calculating age from date of birth in a table (not using a queire). I have tried INT((Date()-[DOB])/365.25) but it does not appear to work.



    Thanks for any assistance.

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    49,220
    Seems to work for me. So what does 'not appear to work' mean - error message, wrong results, nothing happens?
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    oldlearner is offline Advanced Beginner
    Windows 8 Access 2013
    Join Date
    Sep 2015
    Posts
    30
    Hi june. I am getting the message "the expression cannot be used in a calculated column". Am I selecting the wrong data type to use the expression or could it be something else?

  4. #4
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    49,220
    You are doing this in table calculated type field? It does not recognize the Date() function. Not all functions are available to calculated field in table. Do this in query or textbox.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  5. #5
    oldlearner is offline Advanced Beginner
    Windows 8 Access 2013
    Join Date
    Sep 2015
    Posts
    30
    Thanks June will give that a try.

  6. #6
    gpeirce is offline Novice
    Windows 8 Access 2007
    Join Date
    Oct 2015
    Location
    Oregon, USA
    Posts
    13
    I've had very good luck with:

    Age as of Today: DateDiff("yyyy",[dob],Now())-IIf(Format([dob],"mmdd")>Format(Now(),"mmdd"),1,0)

    This subtracts the birth year from the current year. If the person's mmdd birthday has not been reached yet, 1 is subtracted.

    Hope this helps.

  7. #7
    oldlearner is offline Advanced Beginner
    Windows 8 Access 2013
    Join Date
    Sep 2015
    Posts
    30
    Thanks guys. used a text box on a form and is working fine.
    Can you advise if I would need to create and perform a Macro (also advise on contents of macro) to update the ages periodically?

  8. #8
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    49,220
    If by update you mean save age to table - don't. Calculate when needed.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  9. #9
    oldlearner is offline Advanced Beginner
    Windows 8 Access 2013
    Join Date
    Sep 2015
    Posts
    30
    Hi June- Sorry I need to make myself more clear with what I am asking. I actually meant do I need to create a macro to update the ages periodically so they are more accurate when someone has a birthday and is 1 year older.

  10. #10
    Join Date
    Jun 2015
    Location
    Wales. Land of the sheep.
    Posts
    1,228
    What Jane said is right, just have your report calculate this when opened each time. Don't actually save this information.

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

Similar Threads

  1. get age from date to birth
    By earlcools in forum Queries
    Replies: 2
    Last Post: 01-27-2015, 07:03 PM
  2. How to change date of birth into text
    By Ayiramala in forum Access
    Replies: 13
    Last Post: 12-28-2014, 09:21 PM
  3. Calculate age from Date of Birth
    By djcmalvern in forum Programming
    Replies: 8
    Last Post: 05-03-2013, 06:18 AM
  4. Date of birth converter
    By markyboy in forum Forms
    Replies: 1
    Last Post: 05-23-2011, 07:20 AM
  5. Calculate age from birth date
    By mick in forum Forms
    Replies: 2
    Last Post: 07-03-2010, 04:31 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 - Senior Forums