round a date

A

Anvil22

I have the folowing in a query that gives me the age today when compared to a
DOB. The calucation gives me results like this 14.00000000000. How do I round
the age to 14.

age: (Date()-[DOB])/365

Can anyone help.

Thanks
 
R

raskew via AccessMonster.com

Hi -

That method provides, at best, an approximation. Try copying/pasting to a
standard module then call as shown.

Public Function Age4(DOB As Date) As Integer
Age4 = DateDiff("yyyy", DOB, Date) + (Date < DateSerial(year(Date),
month(DOB), Day(DOB)))
End Function

The + (Date < DateSerial(year(Date), month(DOB), Day(DOB))) is a boolean
statement which will return -1 if true, 0 if false.

HTH - Bob
I have the folowing in a query that gives me the age today when compared to a
DOB. The calucation gives me results like this 14.00000000000. How do I round
the age to 14.

age: (Date()-[DOB])/365

Can anyone help.

Thanks
 
J

John W. Vinson

I have the folowing in a query that gives me the age today when compared to a
DOB. The calucation gives me results like this 14.00000000000. How do I round
the age to 14.

age: (Date()-[DOB])/365

Can anyone help.

Thanks

Your expression ignores leap years and won't give accurate results. For the
age as of most recent birthday (what we think of as "age") you can use

DateDiff("yyyy", [DOB], Date()) - IIF(Format([DOB], "mmdd") > Format(Date(),
"mmdd"), 1, 0)

This calculates the number of January 1sts between the DOB and today's date,
and then subtracts one if this year's birthday anniversary is still to come.

John W. Vinson [MVP]
 
A

Anvil22

Also Worked. very much appreciative.
Dick

John W. Vinson said:
I have the folowing in a query that gives me the age today when compared to a
DOB. The calucation gives me results like this 14.00000000000. How do I round
the age to 14.

age: (Date()-[DOB])/365

Can anyone help.

Thanks

Your expression ignores leap years and won't give accurate results. For the
age as of most recent birthday (what we think of as "age") you can use

DateDiff("yyyy", [DOB], Date()) - IIF(Format([DOB], "mmdd") > Format(Date(),
"mmdd"), 1, 0)

This calculates the number of January 1sts between the DOB and today's date,
and then subtracts one if this year's birthday anniversary is still to come.

John W. Vinson [MVP]
 
A

Anvil22

John W. Vinson said:
I have the folowing in a query that gives me the age today when compared to a
DOB. The calucation gives me results like this 14.00000000000. How do I round
the age to 14.

age: (Date()-[DOB])/365

Can anyone help.

Thanks

Your expression ignores leap years and won't give accurate results. For the
age as of most recent birthday (what we think of as "age") you can use

DateDiff("yyyy", [DOB], Date()) - IIF(Format([DOB], "mmdd") > Format(Date(),
"mmdd"), 1, 0)

This calculates the number of January 1sts between the DOB and today's date,
and then subtracts one if this year's birthday anniversary is still to come.

John W. Vinson [MVP]
Thank you
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top