Age Calculation Does not work if DOB is 1929 or Earlier 4

[mellay]

Hi!

I'm using a text box w/ =((Now()-[Birthdate])/365.25)

Works OK EXCEPT when date of birth is 1929 or Earlier.
For example dob of 03/23/23 returns an age of "-19"
For example dob of 01/01/10 returns an age of "-5"

Any ideas? Thank you in advance!! Mellay

 

[MoLaker]

Check out this thread

http://www.tek-tips.com/viewthread.cfm?qid=819256

 

[mellay]

Thanks for Prompt reply!

I changed to idea suggested by mph1:

Age = DateDiff("yyyy", [Birthdate], Now()) + _
Int(Format(Now(), "mmdd") < Format([Birthdate], "mmdd"))

However, Dates earlier than 1929 still show up as a negative number. "-19", etc. All dates from 1930 to present work fine.

Any other adjustments? Ideas? Thanks!! Mellay

 

[MichaelRed]

1930 is the (MS?) selected year to avoid the Y2K bug of a few years ago. To deal with date functions prior to then, you MUST have and use the century as part of the date.




MichaelRed
mlred@verizon.net

 

[mellay]

Thanks Michael!

How would you add the century??

Age = DateDiff("yyyy", [Birthdate], Now()) + _
Int(Format(Now(), "mmdd") < Format([Birthdate], "mmdd"))

Thanks!! Mellay

 

[mellay]

Michael

PS I am using Access 2002 on Windows XP. Thanks!!!! mellay

 

[cmmrfrds]

It might be simplier to just use days.

MyDate = #9/1/1923#
age = (DateDiff("d", MyDate, Now()) / 365.25)

 

[MichaelRed]

See the manner in which cmmrfrds formatter the year of the date. 19[/coloor]23. All date types in MS include the century, as they are actually stored as a "double" with the integer portion being the number of (whole) days since Dec. 30 1899. But (the inevitable butinski) in the date calcs they chose to implicitly assume the century. Before y2k, this was always the then current century, but the 'nervousmess re the turn of the century cause the shift to the thirtith year. However this only applies to the CALCULATION using dates, so data (tables) entry continues to use the current century. Thus hte connumdrum of your situation. To deal with the dates prior to 1930.

Consider the following:

? Clng(#12/30/1899#)
 0 
? Clng(#12/30/1929#)
 10957 
? Clng(#1/1/1930#)
 10959 
? CLng(#1/1/30#)
 10959 
? CLng(#12/30/29#)
 47482

Notice, in particular, the difference between the dates w/o the century at the bottom.

In your expression, the syntax indicates 'Birthdate' is taken from a recordset source, so the real way to correct the issue is to correct the data. You sonot state the extent of the issue within the data base, so the immediate soloution may be as trivial as an ad-hoc query to find all the entries and re-entering the dates, although the same query as an update to add the century would neo be much more difficult. In the longer view, you need to correct the data entry / collection process to force the correct entry.




MichaelRed
mlred@verizon.net

 

[dhookom]

I would not use "/365.25" because this isn't always accurate. From the page

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

Age=DateDiff("yyyy", [Bdate], Now())+ _
Int( Format(now(), "mmdd") < Format( [Bdate], "mmdd") )

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[mellay]

Thanks to Everyone for your invaluable help!! Mellay