Prev: I can't agree with you more about the necessity of
Next: Rounding ages to nearest tenth
From: Gail T. on 24 Jan 2010 00:36
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



From: Tom Wickerath AOS168b AT comcast DOT on 24 Jan 2010 04:14
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
From: Arvin Meyer [MVP] on 24 Jan 2010 07:58
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." <GailT(a)discussions.microsoft.com> wrote in message
news:86DC00BA-C02A-4121-BE74-68DF4910AAFC(a)microsoft.com...
>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
>
>
>


 | 
Pages: 1
Prev: I can't agree with you more about the necessity of
Next: Rounding ages to nearest tenth