Solved

# Update age in a table  MS Access

Posted on 2003-02-19
Medium Priority
490 Views
Hello,
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.

Thanks
0
Question by:csmac71
[X]
###### 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

LVL 4

Expert Comment

ID: 7982243
datediff("y",[dob],Now)
0

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

0

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.

AW
0

LVL 1

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:

DATEDIFF("y", YourTable.DOB, GETDATE())

For VB:

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

Hope this helps,
Tim
0

LVL 18

Expert Comment

ID: 7982720
datediff is unreliable I found, because it works on a 'nearest year' basis not a curtate duration basis.

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.
0

Author Comment

ID: 7993352
Hi,
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.
Per_data.MoveLast
Per_data.MoveFirst

Do Until Per_data.EOF

With Per_data
.Edit

AGE = AGE
DOB = DOB

IntervalType = "yyyy"
FirstDate = !DOB
MyDate = DATE

!AGE = DateDiff(IntervalType, !DOB, MyDate)
.Update
.MoveNext

End With

Loop

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.

0

Author Comment

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

Expert Comment

ID: 8940458
csmac71:
Experts: Post your closing recommendations!  Who deserves points here?
0

LVL 49

Expert Comment

ID: 8959645
Moderator, my recommended disposition is:

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

DanRollins -- EE database cleanup volunteer
0

## Featured Post

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
Course of the Month10 days, 11 hours left to enroll