Age calculation in Query

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

On Tue, 10 Nov 2009 07:31:05 -0800, Dave wrote:

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



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

--
Fred
Please respond only to this newsgroup.
I do not reply to personal e-mail

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

Dave wrote:
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

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


--
Build a little, test a little.


"Dave" wrote:

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

Thank you all

Dave

"Dave" wrote in message
...
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

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.

----
HTH
Dale



"Dave" wrote:

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

On Nov 10, 11:57*am, John Spencer wrote:
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 CountyDave wrote:
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
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.