round a date

  • Thread starter Thread starter Anvil22
  • Start date Start 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
 
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
Click to expand...
 
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
Click to expand...

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]
 
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
Click to expand...

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]
Click to expand...
 
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
Click to expand...

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
Click to expand...
 
Back
Top