Age calculation

[Jian Su]

Hi All,

Can anyone tell me how to do a calculation in a query
which will return a rounded number for an age?

I've got a DOB field - dd/mm/yyyy format - and in a query
I've set up I can subtract the DOB from Date() to get a
total number of days difference. I then divide by 365 to
get the years, but this result in a lot of following
decimal places.

In the properties, I tried setting it to General Number,
0 decimal places, but this doesn't work. The result
remains
the same.

How do I get the query to calculate on the Year component
of the DOB and Date() only, OR to return a whole figure
on the entire date?

A simple thing to do, one would think, but "Help" is
anything but... Can't find any relevant examples and
don't have time to learn programming!

Have a good day,

Jian Su

 

[Ed Warren]

See the discussion thread below 12/15/03 regarding birthday

Ed Warren

 

[Van T. Dinh]

Use the method described in The Access Web:

http://www.mvps.org/access/datetime/date0001.htm

 

[Nikos Yannacopoulos]

Year(Date())-Year([DOB])

 

[Dave B]

Hi,

I had the same problem to get round it i set the properites field within the
query to fixed, and entered int before the calculation it then worked no
problem.

U shud make it divide by 365.25 instead of 365 cos u havent included leap
years.

Years: Int(Now()-[Date of birth])/365.25

Year(Date())-Year([DOB])

-----Original Message-----
Hi All,

Can anyone tell me how to do a calculation in a query
which will return a rounded number for an age?

I've got a DOB field - dd/mm/yyyy format - and in a query
I've set up I can subtract the DOB from Date() to get a
total number of days difference. I then divide by 365 to
get the years, but this result in a lot of following
decimal places.

In the properties, I tried setting it to General Number,
0 decimal places, but this doesn't work. The result
remains
the same.

How do I get the query to calculate on the Year component
of the DOB and Date() only, OR to return a whole figure
on the entire date?

A simple thing to do, one would think, but "Help" is
anything but... Can't find any relevant examples and
don't have time to learn programming!

Have a good day,

Jian Su



.

 

[John Vinson]

Jian (and Dave and Nikos), there's actually a better way, using the
builtin Access date functions. Try

Age: DateDiff("yyyy", [Date Of Birth], Date()) - IIF(Format([Date Of
Birth], "mmdd") > Format(Date(), "mmdd"), 1, 0)

This calculates the age in years, and then corrects for the case where
the birthday has not yet occurred by subtracting 1.

Hi,

I had the same problem to get round it i set the properites field within the
query to fixed, and entered int before the calculation it then worked no
problem.

U shud make it divide by 365.25 instead of 365 cos u havent included leap
years.

Years: Int(Now()-[Date of birth])/365.25

Year(Date())-Year([DOB])

-----Original Message-----
Hi All,

Can anyone tell me how to do a calculation in a query
which will return a rounded number for an age?

I've got a DOB field - dd/mm/yyyy format - and in a query
I've set up I can subtract the DOB from Date() to get a
total number of days difference. I then divide by 365 to
get the years, but this result in a lot of following
decimal places.

In the properties, I tried setting it to General Number,
0 decimal places, but this doesn't work. The result
remains
the same.

How do I get the query to calculate on the Year component
of the DOB and Date() only, OR to return a whole figure
on the entire date?

A simple thing to do, one would think, but "Help" is
anything but... Can't find any relevant examples and
don't have time to learn programming!

Have a good day,

Jian Su



.