 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / Queries / May 2005Datediff Function
Need help with using the Datediff function, first off i am not sure if that is the right function to use but what i am trying to do is to calculate or generate the current age of a person. This what i have at the moment Age: DateDiff (yyyy, date(), [DOB]) and a pop up box keeps coming up asking for a parameter value for the yyyy So what i am given is their birthday and i need to work out their age so i am trying to deduct the current date from the date of birth to give the years the two dates differ. Is this the right way to go about it? Please help Thanks yyyy should be enclosed in double-quotes: Age: DateDiff ("yyyy", date(), [DOB]) ...but that formula will result in an incorrect value. Try this instead: Age: Abs(DateDiff("yyyy", [DOB], Date()) + (Format([DOB], "mmdd") > Format(Date(), "mmdd"))) Regards, Graham R Seach Microsoft Access MVP Sydney, Australia --------------------------- > Need help with using the Datediff function, first off i am not sure if > that [quoted text clipped - 11 lines] > Please help > Thanks Hey thanks guys for the help, was very helpful just want to as Graham though if you could please explain what each of the different sections/functions mean or do. For example why put format in front of dob and date()? and what were you adding etc. Thanks a lot for the help again just wanted to know how you derived it. Cheers MN > Hey thanks guys for the help, was very helpful just want to as Graham > though if you could please explain what each of the different > sections/functions mean or do. For example why put format in front of dob > and date()? and what were you adding etc. Thanks a lot for the help again > just wanted to know how you derived it. Age: Abs(DateDiff("yyyy", [DOB], Date()) + (Format([DOB], "mmdd") > Format(Date(), "mmdd"))) The DateDiff function gives the number of years since DOB, without knowing whether or not the person has had a birthday yet this year. It assumes everyone was born on 1/1. The addition is as follows: If the > condition is true (true condition is represented by -1 in the computer) that means the month and day of birth are greater than the current month and day or to put it another way, the person has NOT had a birthday yet this year. If the person HAS NOT had a birthday yet this year, the > comparison is -1 so you subtract 1 from the number of years to get the age as of today. If the person HAS had a birthday already this year, the > comparison returns 0 so the DateDiff function is correct without subtracting anything. The Format function is a clever shortcut to get the month and day of the dates in question. Tom Lake Pretty close, except for a couple of points. DateDiff() doesn't return the number of years since DOB, nor does it assume everyone was born on 1/1. The DateDiff() function counts time-related boundary crossings. For example, the following will return one day, when only two seconds has actually elapsed. DateDiff("d", #31/12/2004 23:59:59#, #1/1/2005 0:0:1#) Note: I use Australian dates, not US ones - not that it matters here. If you change the interval to "m", it will return one month, and if you change it to "yyyy", it will return one year. So a more correct explanation is that DateDiff() returns the number of year-boundary crossings since DOB. Regards, Graham R Seach Microsoft Access MVP Sydney, Australia --------------------------- >> Hey thanks guys for the help, was very helpful just want to as Graham >> though if you could please explain what each of the different [quoted text clipped - 19 lines] > > Tom Lake Datediff always returns an integer indicating the difference in the 'datepart' of Datediff. Using Datediff using December 31 2004 and January 1 2005 as arguments will return 1 (if you as for years). Regards PerL > Need help with using the Datediff function, first off i am not sure if that > is the right function to use but what i am trying to do is to calculate or [quoted text clipped - 10 lines] > Please help > Thanks |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.