Calculating age

[Gail T.]

I am trying to calculate age in Access. My problem is this –in some
instances I only know the year of birth. Is there a way to set the date/time
field in a table to accept an entry for year only? If not is there a
solution this issue?

(I working on family tree and in some instances I only know the year of
birth and complete date of death)

Thanks

 

[Tom Wickerath]

Hi Gail,

Is there a way to set the date/time field in a table to accept an entry
for year only?

No. You can either enter a fictious month and day (not recommended), or you
can use a text data type instead. If you use a text field, then you can store
any form of date that you want. However, you will likely want to implement
some validation code on a form, so that a user can only enter a valid date or
a year by itself.

You would also need to convert the resulting string to a date on-the-fly in
order to calculate age. Access provides built-in functions for working with
dates including CDate, CVDate, DatePart, DateValue, DateDiff, DateSerial,
etc. For those records where you only have a year available, you could
actually calculate a range, by using January 1 and December 31 as two
month/day inputs.


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
__________________________________________

 

[Arvin Meyer [MVP]]

As Tom mentions a date in terms of data consists of month/day/year. If you
are storing the year, you cannot calculate date, so what you first need to
do is add a month and day. It is a very bad idea to do this with the data,
but you can do it in a query column. I'll assume that those with the year
only have 4 "digits", so a query column can look like:

BDay: IIf(Len([BirthdateField]) = 4, "1/1/" & [BirthdateField],
[BirthdateField])

Now because this is really a text column, wrap the entire expression in
CDate to convert it:

BDay: CDate(IIf(Len([BirthdateField]) = 4, "1/1/" & [BirthdateField],
[BirthdateField]))

Now you'll need to calulate age, and you can do that with this function:

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

 

[Steve Hayes]

I am trying to calculate age in Access. My problem is this –in some
instances I only know the year of birth. Is there a way to set the date/time
field in a table to accept an entry for year only? If not is there a
solution this issue?

(I working on family tree and in some instances I only know the year of
birth and complete date of death)

What I do in that case is to use a text field and enter it in the format
YYYY-MM-DD.

Thus if you know the year only you can enter something like

1978-00-00

and it will sort correctly.

To calculate age you would have to write a UDF (user-defined function) in VBA
or something.