rounding in update query

[j9]

I have a table with a Chronological Age column, defined as Single, fixed,
with auto decimal places. I have an update query that does a DateDiff
function to calculate and populate the field, based on birthdate and a test
date. The property on the calculated field is Fixed and Auto. Here's the
calculation:
DateDiff("yyyy",[DOB],[DatA])

where DatA is the date of the test (not necessarily today's date)

When I run the query, it updates the chronological age column with values
like 9.00, instead of 9.33. But we need to have the fractional age, instead
of the "whole number" age. I've read through all of the posts and still
can't figure out what I'm doing wrong. I'd like to solve this without doing
VB. Is that possible?

Thanks so much!

 

[fredg]

I have a table with a Chronological Age column, defined as Single, fixed,
with auto decimal places. I have an update query that does a DateDiff
function to calculate and populate the field, based on birthdate and a test
date. The property on the calculated field is Fixed and Auto. Here's the
calculation:
DateDiff("yyyy",[DOB],[DatA])

where DatA is the date of the test (not necessarily today's date)

When I run the query, it updates the chronological age column with values
like 9.00, instead of 9.33. But we need to have the fractional age, instead
of the "whole number" age. I've read through all of the posts and still
can't figure out what I'm doing wrong. I'd like to solve this without doing
VB. Is that possible?

Thanks so much!

Sorry but DateDiff() does NOT give fractional values.
Your expression is asking for the difference in year boundaries
between 2 values (i.e. the difference in years between 12/31/2007 and
1/1/2008 using DateDiff() is 1 year).

If you can use data in the form of "25 years 3 months 12 days", use
the Diff2Date function found at:

http://www.accessmvp.com/djsteele/Diff2Dates.html

If you do want data in the decimal form of 9.33 (which is not exact)
you can use

DateDiff("d",[DOB],[DatA]) / 365.25

which will be approximately correct.

In addition to the above, if you did wish to show just the year age at
a particular date, an accurate calculation would be:

In a query:
Age: DateDiff("yyyy",[DOB],[DatA])-IIf(Format([DOB],
"mmdd")>Format([DatA],"mmdd"),1,0)

Directly as the control source of an unbound control:
=DateDiff("yyyy",[DOB],[DatA])-IIf(Format([DOB],
"mmdd")>Format([DatA],"mmdd"),1,0)

Where [DOB] is the birthdate field.

The above takes into effect whether or not the birthday has already
occurred this current year.