Software

Our forums are currently in maintenance mode and the ability to post is disabled. We will be back up and running as soon as possible. Thanks for your patience!

General discussion

Locked

Access formula required

By pudney ·
I have created an Access client database which has a field for "date of birth". How do I get Access to calculate in another field the actual age? I have used the following expression but it does not seem to work.
Int((Date()-[Client]![Client-DOB])/365)
Many thanks

This conversation is currently closed to new comments.

Thread display: Collapse - | Expand +

All Comments

Collapse -

by DKlippert In reply to Access formula required

Here are some alternative answers
www.mvps.org/access/datetime/date0001.htm
(Q) How do I calculate the age of a person given his/her birthdate?

(A) There are several methods to do this. I'll list two methods that have been posted to the newsgroups in the recent past:

Assuming that the birthdate field is called [BDate] and is of type date, you can use the following calculation

Age=DateDiff("yyyy", [Bdate], Now())+ _
Int( Format(now(), "mmdd") < Format( [Bdate], "mmdd") )

Alternate: You can use this function to calculate Age.

Function Age(Bdate, DateToday) As Integer
' Returns the Age in years between 2 dates
' Doesn't handle negative date ranges i.e. Bdate > DateToday

If Month(DateToday) < Month(Bdate) Or (Month(DateToday) = _
Month(Bdate) And Day(DateToday) < Day(Bdate)) Then
Age = Year(DateToday) - Year(Bdate) - 1
Else
Age = Year(DateToday) - Year(Bdate)
End If
End Function

Here's another detailed Age Checker.
'--- CODE START ---
Public Sub CalcAge(vDate1 As Date, vdate2 As Date, ByRef vYears As Integer,
ByRef vMonths As Integer, ByRef vDays As Integer)
' Comments : calculates the age in Years, Months and Days
' Parameters:
' vDate1 - D.O.B.
' vDate2 - Date to calculate age based on
' vYears - will hold the Years difference
' vMonths - will hold the Months difference
' vDays - will hold the Days difference
vMonths = DateDiff("m", vDate1, vdate2)
vDays = DateDiff("d", DateAdd("m", vMonths, vDate1), vdate2)
If vDays < 0 Then
' wierd way that DateDiff works, fix it here
vMonths = vMonths - 1
vDays = DateDiff("d", DateAdd("m", vMonths, vDate1), vdate2)
End If
vYears = vMonths \ 12 ' integer division
vMonths = vMonths Mod 12 ' only want leftover less than one year
End Sub
'--- CODE END ---

Collapse -

by pudney In reply to

Sorry this didn't help me. I must be putting the expression in the worng place or something.

Thanks for your help anyway.

Collapse -

by timwalsh In reply to Access formula required

In Access, you can not create a calculated field in a Table.

In order to perform your calculation, you would need to create a Query that performs the calculation.

I would suggest using a different function also. The DateDiff function will calculate the difference between two dates with the result dependent on the input paremeters.

In your case, the syntax would be:

DateDiff("yyyy",[Client]![Client-DOB],NOW())

where

"yyyy" denotes the difference will be calculated in years
NOW() is the expression that returns the current date.

Hope this helps.

Collapse -

by pudney In reply to

Thank you - I couldn't get this to work but it put me onto the idea of a query and found an "update query" gave an answer. Of course it means updating all the time which could be a real pain. May not include it in the final database.

Collapse -

by pudney In reply to Access formula required

This question was closed by the author

Related Discussions

Related Forums