Calculating age

  • Thread starter Thread starter Gail T.
  • Start date Start date
G

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
 
Hi Gail,
Is there a way to set the date/time field in a table to accept an entry
for year only?
Click to expand...

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/
__________________________________________
 
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
 
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)
Click to expand...

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.
 
Back
Top