Update age in a table  MS Access

Posted on 2003-02-19
Medium Priority
Last Modified: 2008-03-03
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.

Question by:csmac71
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions

Expert Comment

ID: 7982243
LVL 18

Expert Comment

ID: 7982312
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

LVL 44

Expert Comment

ID: 7982340
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.

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!


Accepted Solution

tgraffham earned 200 total points
ID: 7982367
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,
LVL 18

Expert Comment

ID: 7982720
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.

Author Comment

ID: 7993352
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.


Author Comment

ID: 7994341
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!

Expert Comment

ID: 8940458
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?
LVL 49

Expert Comment

ID: 8959645
Moderator, my recommended disposition is:

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

DanRollins -- EE database cleanup volunteer

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Enums (shorthand for ‘enumerations’) are not often used by programmers but they can be quite valuable when they are.  What are they? An Enum is just a type of variable like a string or an Integer, but in this case one that you create that contains…
This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…
Suggested Courses

765 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question