I am trying to set up access to calculate ages.

[Guest]

I am trying to set up access to calculate ages. Does anyone have an idea on
how to do this?

 

[Douglas J Steele]

Check http://www.mvps.org/access/datetime/date0001.htm at "The Access Web"

 

[Guest]

=BAge: Int((DateDiff("d",[Birthdate],Now())/365.25))

Where [birthdate] is a text field and [bAge] is a second column in a query

 

[John Vinson]

On Tue, 29 Nov 2005 11:00:18 -0800, Edwin Huellstrounk <Edwin

I am trying to set up access to calculate ages. Does anyone have an idea on
how to do this?

You should not store the age anywhere - if you do, it will be wrong
next year anyway.

Store the DOB (Date Of Birth) field in a Date/Time field and use a
calculated field in a Query:

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

The IIF function corrects the age if the birthdate has not yet arrived
this year.

John W. Vinson[MVP]

 

[John Vinson]

=BAge: Int((DateDiff("d",[Birthdate],Now())/365.25))

Where [birthdate] is a text field and [bAge] is a second column in a query

This will give an inaccurate result on (or the day before or after)
the birthday, more often than not. See the other replies in this
thread for better alternatives.

Also you can't have it both ways - you can use the syntax

BAge: <expression>

in a vacant Field cell in a query, or you can use

= <expression>

in the Control Source of a textbox on a form or report - but you can't
do both.

John W. Vinson[MVP]

 

[Br@dley]

On Tue, 29 Nov 2005 11:00:18 -0800, Edwin Huellstrounk <Edwin

I am trying to set up access to calculate ages. Does anyone have an
idea on how to do this?

You should not store the age anywhere - if you do, it will be wrong
next year anyway.

Store the DOB (Date Of Birth) field in a Date/Time field and use a
calculated field in a Query:

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

The IIF function corrects the age if the birthdate has not yet arrived
this year.

John W. Vinson[MVP]

I use

=DateDiff("yyyy",[Birthdate],Now())+Int(Format(Now(),"mmdd")<Format([Birthdate],"mmdd"))

Same method, just a different syntax
--
regards,

Bradley

A Christian Response
http://www.pastornet.net.au/response

 

[John Vinson]

=DateDiff("yyyy",[Birthdate],Now())+Int(Format(Now(),"mmdd")<Format([Birthdate],"mmdd"))

Same method, just a different syntax

I used to, but someone pointed out that True is -1 in JET, +1 in
SQL/Server - so it limits the validity!

Works just fine in Access though and is likely a bit quicker, too.

John W. Vinson[MVP]

 

[Br@dley]

=DateDiff("yyyy",[Birthdate],Now())+Int(Format(Now(),"mmdd")<Format([Birthdate],"mmdd"))

Same method, just a different syntax

I used to, but someone pointed out that True is -1 in JET, +1 in
SQL/Server - so it limits the validity!

Works just fine in Access though and is likely a bit quicker, too.

It wouldn't matter since Int() would return 1 for 1 and -1 wouldn't it?

--
regards,

Bradley

A Christian Response
http://www.pastornet.net.au/response

 

[John Vinson]

It wouldn't matter since Int() would return 1 for 1 and -1 wouldn't it?

Nope. Abs() would, but Int() just truncates any fractional part:

?int(-1)
-1
?int(-1.1)
-2
?int(-0.9)
-1


John W. Vinson[MVP]

 

[Br@dley]

Nope. Abs() would, but Int() just truncates any fractional part:

?int(-1)
-1
?int(-1.1)
-2
?int(-0.9)
-1

Ah yes, of course.
--
regards,

Bradley

A Christian Response
http://www.pastornet.net.au/response