I have a database where I enter the person's date of birth. The users of the
database want to be able to see the age of the person as of the day they are
viewing. I thought this would be simple and entered the following into a
query to provide an age field for the form:

DateDiff("yyyy",[PatientDOB],Now())

However, it appears that this is only accurate some of the time and with the
new year I have noticed that this is more prominent. Is there a better
formula for working this out? It doesn't necessarily have to be in the query
as it can be calculated in a field that updates when the form is refreshed.

On 7 jan, 11:54, Rich Stone <RichSt...@discussions.microsoft.com>
wrote:
> I have a database where I enter the person's date of birth. The users of the
> database want to be able to see the age of the person as of the day they are
> viewing. I thought this would be simple and entered the following into a
> query to provide an age field for the form:
>
> DateDiff("yyyy",[PatientDOB],Now())
>
> However, it appears that this is only accurate some of the time and with the
> new year I have noticed that this is more prominent. Is there a better
> formula for working this out? It doesn't necessarily have to be in the query
> as it can be calculated in a field that updates when the form is refreshed.

You could build your own function and use it in the query. The
function looks like:

Function Age(DateOfBirth As Date) As Byte
If Format(DateOfBirth, "mmdd") > Format(Date, "mmdd") Then
Age = DateDiff("yyyy", DateOfBirth, Date) - 1
Else
Age = DateDiff("yyyy", DateOfBirth, Date)
End If
End Function


Groeten,

Peter
http://access.xps350.com

Thanks for your help. Works perfectly!

"BruceM via AccessMonster.com" wrote:

> Here is a link to a few different methods:
>
> http://www.mvps.org/access/datetime/date0001.htm
>
> One of the methods is similar to what was posted, except uses the Year
> function rather than formatted values. Another method shows how to get years,
> months, and days, if you want that level of detail.
>
> Rich Stone wrote:
> >I have a database where I enter the person's date of birth. The users of the
> >database want to be able to see the age of the person as of the day they are
> >viewing. I thought this would be simple and entered the following into a
> >query to provide an age field for the form:
> >
> >DateDiff("yyyy",[PatientDOB],Now())
> >
> >However, it appears that this is only accurate some of the time and with the
> >new year I have noticed that this is more prominent. Is there a better
> >formula for working this out? It doesn't necessarily have to be in the query
> >as it can be calculated in a field that updates when the form is refreshed.
>
> --
> Message posted via AccessMonster.com
> http://www.accessmonster.com/Uwe/For...ccess/201001/1
>
> .
>