addin or subtracting dates before 1900??

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

amknorr - 18 Jul 2007 16:36 GMT

I geneaology work we have to work with dates prior to 1900. How can I find
the age of a person born in 1893 who expired in 1960?

Reply to this Message

Allen Browne - 18 Jul 2007 16:48 GMT

See the Age() function here:
http://allenbrowne.com/func-08.html

You can pass in the death date as the 2nd parameter, e.g.:
? Age([BirthDate], [DeathDate])
If the person hasn't died yet, it returns their age as of today.

DateDiff() can calculate a difference in years, but you need to adjust if
the person has not had their birthday in the year they died.

Signature

Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

>I geneaology work we have to work with dates prior to 1900. How can I find
> the age of a person born in 1893 who expired in 1960?

Reply to this Message

Pixie78 - 18 Jul 2007 17:00 GMT

I was just reading about this and made a sample for to see if it worked for
dates before 1900 and it does. Use DateDiff. Here is the code you would use
in a text box control source of your form or report.

=DateDiff("yyyy", [DOB], [DOE])

the "yyyy" will calculate the time is years. "d" Days and so on. DOB would
be the name of your field for Date of Birth and I used DOE for the name of
your field for Date of Expiration.
I used it just now to make sure it worked before I posted it to you and it
does. I put in several different dob's in the 1800's and all the date of
deaths over 1900 and it works great.

> I geneaology work we have to work with dates prior to 1900. How can I find
> the age of a person born in 1893 who expired in 1960?

Reply to this Message

John W. Vinson - 18 Jul 2007 20:21 GMT

>I was just reading about this and made a sample for to see if it worked for
>dates before 1900 and it does. Use DateDiff. Here is the code you would use
[quoted text clipped - 5 lines]
>be the name of your field for Date of Birth and I used DOE for the name of
>your field for Date of Expiration.

That will be close... but not absolutely accurate, at least by the traditional
use of "age". DateDiff actually counts year (or day, or second, or...)
boundaries, not full years; so if [DOB] were 12/31/1875 and [DOE] were
1/1/1876 - just a day later - the age would be reported as one year. Or if
[DOB] were 1/1/1931 and [DOE] 12/29/1931 - almost a year - you'ld still get 0.

You can correct for this with an expression:

DateDiff("yyyy", [DOB], [DOE]) - IIF(Format([DOE], "mmdd") > Format([DOB],
"mmdd"), 1, 0)

John W. Vinson [MVP]

Reply to this Message

Pieter Wijnen - 25 Sep 2007 16:21 GMT

Or simplified to

DateDiff("yyyy", [DOB], [DOE]) + (Format([DOE], "mmdd") > Format([DOB],
"mmdd"))

I love Boolean expressions <g>

Pieter

>>I was just reading about this and made a sample for to see if it worked
>>for
[quoted text clipped - 23 lines]
>
> John W. Vinson [MVP]

Reply to this Message

John W. Vinson - 25 Sep 2007 17:46 GMT

On Tue, 25 Sep 2007 17:21:26 +0200, "Pieter Wijnen"
<it.isi.llegal.to.send.unsollicited.mail.wijnen.nospam.please@online.replace.with.norway>
wrote:

>Or simplified to
>
>DateDiff("yyyy", [DOB], [DOE]) + (Format([DOE], "mmdd") > Format([DOB],
>"mmdd"))
>
>I love Boolean expressions <g>

That's what I used to use... until someone pointed out that in SQL/Server True
is +1 rather than -1. The IIF is at least platform independent!

John W. Vinson [MVP]

Reply to this Message

Pieter Wijnen - 25 Sep 2007 23:05 GMT

Didnt' know SQLServer had IIf, Format & DateDiff <g>
the statement will only be valid in VBA anyway

Pieter

> On Tue, 25 Sep 2007 17:21:26 +0200, "Pieter Wijnen"
> <it.isi.llegal.to.send.unsollicited.mail.wijnen.nospam.please@online.replace.with.norway>
[quoted text clipped - 12 lines]
>
> John W. Vinson [MVP]

Reply to this Message

Pieter Wijnen - 25 Sep 2007 23:09 GMT

and using Abs is always an option <g> - I Still love Boolean math
DateDiff("yyyy", [DOB], [DOE]) - Abs(Format([DOE], "mmdd") > Format([DOB],
"mmdd"))

It doesn't get really tricky 'till you move to Oracle (PL/SQL) That does
really have the concepts TRUE & FALSE

Pieter

Reply to this Message

Pat Hartman (MVP) - 18 Jul 2007 19:19 GMT

Keep in mind that you will always want to display four-digit years in this
application since you are working with data that spans centuries.

>I geneaology work we have to work with dates prior to 1900. How can I find
> the age of a person born in 1893 who expired in 1960?

Reply to this Message