Results 1 to 8 of 8
  1. #1
    datahead is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    May 2015
    Location
    Missouri, USA
    Posts
    19

    Calculating age with Computed Column Specification.

    I have spent a couple hours searching for this online. I also searched here and got 246 results. I apologize if I am asking a duplicate question here but my brain is mashed potatoes at this point.




    In my Consumer table I have a datetime column where we store a person's birth date. Simple. I need a column that will store their age based on that date. From what I have found so far this seems overtly complicated.

    I created a column and under the Computed Column Specification I dropped in the formula (datediff(year,[Date of Birth],getdate()).

    I know that naming a column "[Date of Birth]" is a bad practice. The sloppy naming of columns will be corrected in my next big revision of the entire database.

    That being said, (datediff(year,[Date of Birth],getdate()) gives me an accurate age down to the year only. A person who was born in November of 1956 is showing to be 59 years old even though their birthday is still three months away.

    (datediff(dayofyear,[Date of Birth],getdate()) gives me the number of days they have been alive and (datediff(dayofyear,[Date of Birth],getdate())/(365)) gives me the number of years they have been alive and then expresses the months as a decimal (58.71321 years old).


    I know what you think I am about to ask.......

    Is there a way to modify the scale of this column so that it removes everything to the right of the decimal point? It will not let me change it in Access. If I can hide the decimals my coworkers will just see the (mostly) correct age of the consumer on the form.

    I am not above just painting a box over the text field on the form to hide the decimals if I have to.

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Apparently can't set DecimalPlaces on Calculated field in table.

    Did you try setting textbox Format and DecimalPlaces properties? Works for me.
    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
    orange's Avatar
    orange is online now Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Do not store Age.
    Calculate the Age based on DateOfBirth

    Here is a sample function
    Code:
    '---------------------------------------------------------------------------------------
    ' Procedure : Age
    ' Author    : Jack (from awf)
    ' Date      : 06-09-2012
    ' Purpose   : This routine determines the Age of a Person given their DOB.
    ' It accounts for the birthday this year (whether passed or not). A second parameter
    ' Specdate allows you to work from a different Date than today's date.
    'If SpecDate is missing, the routine defaults to today's date.
    '---------------------------------------------------------------------------------------
    ' Last Modified:
    '
    ' Inputs: N/A
    ' Dependency: N/A
    '--------------------------------------------------------------------------
    '
    Public Function Age(dteDOB As Date, Optional SpecDate As Variant) As Integer
        Dim dteBase As Date, intCurrent As Date, intEstAge As Integer
    10  On Error GoTo Age_Error
    
    20  If IsMissing(SpecDate) Then
    30      dteBase = Date
    40  Else
    50      dteBase = SpecDate
    60  End If
    70  intEstAge = DateDiff("yyyy", dteDOB, dteBase)
    80  intCurrent = DateSerial(Year(dteBase), Month(dteDOB), Day(dteDOB))
    90  Age = intEstAge + (dteBase < intCurrent)
    
    100 On Error GoTo 0
    110 Exit Function
    
    Age_Error:
    
    120 MsgBox "Error " & Err.number & " (" & Err.Description & ") in procedure Age of Module AWF_Related"
    End Function

  4. #4
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Orange, OP is using a Calculated field, not actually storing the calculated result. The calculation is dynamic.
    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
    ranman256's Avatar
    ranman256 is online now VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    Youd have to build a function like:
    Code:
    Public Function calcAge(ByVal pvDate)
    Dim y, m, d, Yrs
    y = Year(pvDate)
    m = Month(pvDate)
    d = Day(pvDate)
    
    
    Yrs = Year(Date) - y
    Select Case True
      Case (Month(Date) > m)
          'no
      Case (Month(Date) = m) And Day(Date) >= d
          'no
      Case Else
         Yrs = Yrs - 1
    End Select
    calcAge = Yrs
    End Function
    or

    Code:
    calcAge = (Year(Date) - y) + IIf((Month(Date) > m), 0, IIf((Month(Date) = m) And Day(Date) >= d, 0, -1))

  6. #6
    orange's Avatar
    orange is online now Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716

  7. #7
    datahead is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    May 2015
    Location
    Missouri, USA
    Posts
    19
    Quote Originally Posted by June7 View Post
    Apparently can't set DecimalPlaces on Calculated field in table.

    Did you try setting textbox Format and DecimalPlaces properties? Works for me.
    In the form if I set the Decimal Places to "0" it rounds the number up!!!????!!!! This effectively gives me the same result as (datediff(year,[Date of Birth],getdate()).

  8. #8
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Okay, try wrapping your expression in Int() function.

    However, it might not be available to Calculated field. In which case will have to use it in textbox expression or query.
    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.

Similar Threads

  1. Replies: 3
    Last Post: 01-19-2015, 12:34 PM
  2. Calculating a column
    By cknutzen in forum Access
    Replies: 1
    Last Post: 05-03-2013, 02:00 PM
  3. Total Column Calculating Error
    By joannakf in forum Queries
    Replies: 3
    Last Post: 02-10-2012, 11:17 AM
  4. Calculated fields / Computed columns
    By goodguy in forum Programming
    Replies: 11
    Last Post: 09-18-2011, 02:18 PM
  5. Calculating the column value
    By access in forum Queries
    Replies: 6
    Last Post: 08-20-2009, 11:51 AM

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