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

x
• Status: Solved
• Priority: Medium
• Security: Public
• Views: 506

# Update age in a table MS Access

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
csmac71
1 Solution

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

Commented:
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

Commented:
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

Commented:
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

Commented:
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 Commented:
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 Commented:
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

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

Commented:
Moderator, my recommended disposition is:

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

DanRollins -- EE database cleanup volunteer
0

## Featured Post

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