Round Down in Microsoft Access 2000

[Chrissy]

So I have been working on this form to try and calculate an age from a
birthdate. Everything I have tried has not worked. So I finally came
up with this formula and now it rounds everything up or down. I need
it to round down and I can't figure out for anything. The formula I
have is this:
=DateDiff("d",[Date of Birth],Now())/365)

How can I get this so it rounds down everytime so I can get the correct
age?
Thanks

 

[Douglas J. Steele]

Try using the following to calculated age:

=DateDiff("yyyy", [DateOfBirth], Date()) - _
IIf(Format(Date(), "mmdd") < Format([DateOfBirth], "mmdd"), 1, 0)

DateDiff is very literal. When using yyyy (to calculate the number of years
between two dates), it counts every year boundary it crosses. This means
that DateDiff("yyyy", #2006-12-31#, #2007-01-01#) is 1, even though it's
only 1 day between the two dates. To compensate for this, you need to check
whether or not the birthday has already occurred in the current year. If it
hasn't, you subtract 1 from what DateDiff tells you.

 

[Chrissy]

Awesome... I did it and it worked. thanks so much I have been trying
this forever.

Try using the following to calculated age:

=DateDiff("yyyy", [DateOfBirth], Date()) - _
IIf(Format(Date(), "mmdd") < Format([DateOfBirth], "mmdd"), 1, 0)

DateDiff is very literal. When using yyyy (to calculate the number of years
between two dates), it counts every year boundary it crosses. This means
that DateDiff("yyyy", #2006-12-31#, #2007-01-01#) is 1, even though it's
only 1 day between the two dates. To compensate for this, you need to check
whether or not the birthday has already occurred in the current year. If it
hasn't, you subtract 1 from what DateDiff tells you.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

So I have been working on this form to try and calculate an age from a
birthdate. Everything I have tried has not worked. So I finally came
up with this formula and now it rounds everything up or down. I need
it to round down and I can't figure out for anything. The formula I
have is this:
=DateDiff("d",[Date of Birth],Now())/365)

How can I get this so it rounds down everytime so I can get the correct
age?
Thanks