Age calculation in Query

[Dave]

Access 2003

I am trying to calculate the age of a person directly in the query.
Not sure it is possible but here is what I have tried that does not work.
---
Age: today()-[DOB]

Undefined Function

---
Age: Now()-[DOB]

returns a weird number
17938.3098726852
my guess is that it is counting days
---
So I try dividing by 365
Age: (Now()-[DOB])/365
But I can not format the field to show no decimal places.


How can I get an AGE calculation (in years only)

Thanks
Dave

__________ Information from ESET NOD32 Antivirus, version of virus signature database 4592 (20091110) __________

The message was checked by ESET NOD32 Antivirus.

http://www.eset.com

 

[fredg]

Access 2003

I am trying to calculate the age of a person directly in the query.
Not sure it is possible but here is what I have tried that does not work.

1) If you are going to use Access you are gong to have to learn what
the Access functions are.
There is NO Today() function in Access.

If you wish today's date, then Date() will provide that.
If you wish today's date and time of day, then Now() will provide
that.

---
Age: Now()-[DOB]

returns a weird number
17938.3098726852
my guess is that it is counting days

I haven't check this but yes, it would appear to be the number of days
between today's date and the DOB.

---
So I try dividing by 365
Age: (Now()-[DOB])/365
But I can not format the field to show no decimal places.

How can I get an AGE calculation (in years only)

In a query:

In a query:
Age: DateDiff("yyyy",[DOB],Date())-IIf(Format([DOB],
"mmdd")>Format(Date(),"mmdd"),1,0)

Directly as the control source of an unbound control:
=DateDiff("yyyy",[DOB],Date())-IIf(Format([DOB],
"mmdd")>Format(Date(),"mmdd"),1,0)

Where [DOB] is the birthdate field.

You do know, I hope, that this Age computation should NOT be stored
in any table. Just compute it and display it on a form or report, as
needed.

 

[John Spencer]

If dividing by 365 is close enough then you can use integer division to return
an integer or you can use the Int function to truncate the number

Age: (Date()-[DOB]) \ 365

OR

Age: Int((Date()-[DOB])/365)

If you are interested in a more precise calculation which handles leap years
then use this:

Age: DateDiff("yyyy",DOB,Date())+Int(Format(DOB,"mmdd")>Format(Date(),"mmdd"))


John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

 

[KARL DEWEY]

This --
Age: (Now()-[DOB])\365

 

[Dave]

Thank you all

Dave

Access 2003

I am trying to calculate the age of a person directly in the query.
Not sure it is possible but here is what I have tried that does not work.
---
Age: today()-[DOB]

Undefined Function

---
Age: Now()-[DOB]

returns a weird number
17938.3098726852
my guess is that it is counting days
---
So I try dividing by 365
Age: (Now()-[DOB])/365
But I can not format the field to show no decimal places.


How can I get an AGE calculation (in years only)

Thanks
Dave
__________ Information from ESET NOD32 Antivirus, version of virus
signature database 4592 (20091110) __________

The message was checked by ESET NOD32 Antivirus.

http://www.eset.com




__________ Information from ESET NOD32 Antivirus, version of virus
signature database 4593 (20091110) __________

The message was checked by ESET NOD32 Antivirus.

http://www.eset.com

__________ Information from ESET NOD32 Antivirus, version of virus signature database 4593 (20091110) __________

The message was checked by ESET NOD32 Antivirus.

http://www.eset.com

 

[Dale Fye]

A more accurate method is:

datediff("yyyy", dob, date()) + (Format(Date(), "mmdd") < format(dob, "mmdd"))

The date diff function will return the difference between the numeric values
of the YEAR(DOB) and Year(Date()). Because of this you need adjust that
value based on whether the birthday has been reach in the current year. If
not, you need to subtract a year.

 

[JONEPONE]

If dividing by 365 is close enough then you can use integer division to return
an integer or you can use the Int function to truncate the number

    Age: (Date()-[DOB]) \ 365

OR

    Age: Int((Date()-[DOB])/365)

If you are interested in a more precise calculation which handles leap years
then use this:

Age: DateDiff("yyyy",DOB,Date())+Int(Format(DOB,"mmdd")>Format(Date(),"mmdd"))

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute

Access 2003

I am trying to calculate the age of a person directly in the query.
Not sure it is possible but here is what I have tried that does not work.

Undefined Function

returns a weird number
17938.3098726852
my guess is that it is counting days

How can I get an AGE calculation (in years only)

Thanks
Dave
__________ Information from ESET NOD32 Antivirus, version of virus
signature database 4592 (20091110) __________

The message was checked by ESET NOD32 Antivirus.

http://www.eset.com

Thanks so much for this resolution. I used the longer expression to
include leap years and it worked great and was so much easier than
anything else I came across in my search. Thanks again.