Results 1 to 6 of 6

Age

  1. #1
    mrkaye is offline Advanced Beginner
    Windows 7 Access 2010 (version 14.0)
    Join Date
    Nov 2010
    Posts
    43

    Age


    I have a field named Dob, i want to creat query with the age in years, months, days. can you tell me how to write the expression for diff2date. Or direct me to a web site that has it.

  2. #2
    ssanfu is offline Master of Nothing
    Windows 2K Access 2000
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    There are many sites that have ways to calculate ages. Here is one site:

    http://access.mvps.org/access/datetime/date0001.htm

  3. #3
    mrkaye is offline Advanced Beginner
    Windows 7 Access 2010 (version 14.0)
    Join Date
    Nov 2010
    Posts
    43
    Quote Originally Posted by mrkaye View Post
    I have a field named Dob, i want to creat query with the age in years, months, days. can you tell me how to write the expression for diff2date. Or direct me to a web site that has it.
    Thanks, I will give it a try, was hoping for an expression, diff2dates any ideas.

  4. #4
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2007
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    53,632
    Google: DateDiff
    http://msdn.microsoft.com/en-us/libr...9(SQL.80).aspx
    Problem with DateDiff is rounding. The results you get might not be quite as you expect because you want age down to the day. That is why ssansu suggested that link. What that example shows is a custom public procedure that can be called in an expression just as you would the intrinsic DateDiff. It is shown as a Sub but easy to modify to a Function.
    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
    mrkaye is offline Advanced Beginner
    Windows 7 Access 2010 (version 14.0)
    Join Date
    Nov 2010
    Posts
    43

    Age

    if i could get the syntax for the expression please, access 2010 Query
    i have a field called "DOB" the date is a short date.

    what i would like to see for the end results is.

    Example would be, 39 years 11 months 27 days this would be called the "Age" field
    hope someone can help
    thanks to all

  6. #6
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2007
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    53,632
    You need to build the Function procedure in a general code module. You would need to add a line in the function that would concatenate the date parts into one string that would be returned by the function. Then you can call the procedure from anywhere - VBA or textbox or query. In the ControlSource for a textbox would be: = CalcAge(DOB, Date())

    If you are not familiar with writing code, check out some tutorials. Google: VBA Access custom function
    Here is one http://www.helium.com/items/220687-h...crosoft-access
    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.

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

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