If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
Age calculation in Query
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 |
#3
|
|||
|
|||
Age calculation in Query
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 |
#4
|
|||
|
|||
Age calculation in Query
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 |
#5
|
|||
|
|||
Age calculation in Query
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 |
#6
|
|||
|
|||
Age calculation in Query
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 |
#7
|
|||
|
|||
Age calculation in Query
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. |
Thread Tools | |
Display Modes | |
|
|