General discussion


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.
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
(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
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:



"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