Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 506
  • Last Modified:

Update age in a table MS Access

I havea form to input data to a table.  Onoe field I need in the table is age.  i have a field DOB in short date format.  How can I get the exact age from the DOB and enter it into the table?  The age must be precise, for example someone born in november 1970 must show as 32 in the table till his birthday in November despite 2003 - 1970 = 33.

I would like to use SQL as it seems more simple than VBA.

1 Solution
try this:

Private Sub Command1_Click()
MsgBox CalculateAge("5/31/1976")

End Sub

Function CalculateAge(varBirthDate As Variant) As Integer

   Dim varAge As Variant
   If Not IsDate(varBirthDate) Then Exit Function
   varAge = DateDiff("yyyy", varBirthDate, Now)
   If Date < DateSerial(Year(Now), Month(varBirthDate), _
                        Day(varBirthDate)) Then
      varAge = varAge - 1
   End If
   CalculateAge = CInt(varAge)
End Function

there is a MAJOR problem with the design of your table if you have a field called AGE, which MUST accuarately reflect the current age of the person.  In order for that condition to be correct ALL THE TIME, since you have no way of knowing before hand which dates are in fact birth-dates of any of the people listed in your table, you must therefore re-compute the AGE, for EVERY record, EVERY day.

This is one example of why it is a VERY bad idea to have a CALCULATED field in any table in your database.  The Calculation can benn EASILY doen WHENEVER it is needed, for display on a form, or on a report, use a Query.  And since the calcualtion is done WHEN it is needed, and ONLY for the Record for which it is needed, then it will be accurate - at THAT point in time.

Just a comment.

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

For Oracle SQL, I believe you'd use:

TRUNC((MONTHS_BETWEEN(SYSDATE, TO_DATE(YourTable.DOB, "m/d/yyyy"))/12), 0)

For MS SQL or Access use:


For VB:

DateDiff("y", YourTable.DOB, Now())

Hope this helps,
datediff is unreliable I found, because it works on a 'nearest year' basis not a curtate duration basis.

How about this for an Access DB

SELECT year(now) - year(dob) - iif(format(now,"mmdd") < format(dob,"mmdd"),1,0) AS age
FROM tblDates;

Arthur Wood is right, you don't want to hold 'age now' as a field, because it constantly changes - hold the DOB only, then calculate it when you need it.
csmac71Author Commented:
The AGE field is a required part of the table and needs to be constant.  This is legacy but has made life very easy.  DOB is always populated in the table and it makes it very simple if the age is already calculated rather than having to run a query, etc.  This is what exists now and it does work in that it is seemless and does not slow any processes down:

Public Sub Date_Conversion()

    Dim Personnel As Database
    Dim Per_data As Recordset
    Dim DOB As String
    Dim AGE As String
    Dim MyDate
    Dim IntervalType As String
    Set Personnel = OpenDatabase("Personnel.mdb")
    Set Per_data = Personnel.OpenRecordset("Per_data")
    ' Populate Recordset object.
    ' Return to first record.
    Do Until Per_data.EOF
    With Per_data
    AGE = AGE
    DOB = DOB
    IntervalType = "yyyy"
    FirstDate = !DOB
    MyDate = DATE
    !AGE = DateDiff(IntervalType, !DOB, MyDate)
    End With


End Sub

This works great to keep the AGE field always populated without any effort.  The only drawback is that if the person has not had their birthday yet.  Excel may be a solution, pulling the table out entirely and making it a spreadsheet and having the DB link to it.  AGE is just always needed so to constantly run queries etc isn't good, easier to have it run right once and populate the table for the day and it is good for the whole day.

I am going to try some of your code and SQL, thanks all!  Will let you know what works.

csmac71Author Commented:
Your MS SQL for Access seems to work great!  I added a /365.25 to get the precise age in years but other than that it works like a charm.  It automatically runs when the database opens and I never think about it.  Thanks!
This old question needs to be finalized -- accept an answer, split points, or get a refund.  For information on your options, please click here-> http:/help/closing.jsp#1 
Experts: Post your closing recommendations!  Who deserves points here?
Moderator, my recommended disposition is:

    Accept tgraffham's comment(s) as an answer.

DanRollins -- EE database cleanup volunteer

Featured Post

[Webinar] Database Backup and Recovery

Does your company store data on premises, off site, in the cloud, or a combination of these? If you answered “yes”, you need a data backup recovery plan that fits each and every platform. Watch now as as Percona teaches us how to build agile data backup recovery plan.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now