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?

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/
__________________________________________

"Gail T." wrote:

> 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

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
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com


"Gail T." <(E-Mail Removed)> wrote in message
news:86DC00BA-C02A-4121-BE74-(E-Mail Removed)...
>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
>
>
>

On Sat, 23 Jan 2010 21:36:01 -0800, Gail T. <(E-Mail Removed)>
wrote:

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


--
Steve Hayes from Tshwane, South Africa
Web: http://hayesfam.bravehost.com/stevesig.htm
Blog: http://methodius.blogspot.com
E-mail - see web page, or parse: shayes at dunelm full stop org full stop uk