E
Edwin Huellstrounk
I am trying to set up access to calculate ages. Does anyone have an idea on
how to do this?
how to do this?
D
Douglas J Steele
Check http://www.mvps.org/access/datetime/date0001.htm at "The Access Web"
P
Pete
=BAge: Int((DateDiff("d",[Birthdate],Now())/365.25))
Where [birthdate] is a text field and [bAge] is a second column in a query
Where [birthdate] is a text field and [bAge] is a second column in a query
J
John Vinson
On Tue, 29 Nov 2005 11:00:18 -0800, Edwin Huellstrounk <Edwin
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]
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]
J
John Vinson
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]
B
Br@dley
John said:
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
J
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]
B
Br@dley
John said:=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
J
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]
B
Br@dley
John said:
Ah yes, of course.
--
regards,
Bradley
A Christian Response
http://www.pastornet.net.au/response