Datediff Function

[MN via AccessMonster.com]

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

 

[Graham R Seach]

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

 

[Per Larsen]

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

 

[MN via AccessMonster.com]

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

 

[Tom Lake]

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

 

[Graham R Seach]

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