How do I code to calculate Age? 1

[gadsbywc]

I'm trying to create the code to subtract the "Date of Birth" from the "Current Date", and update the "AGE" field automatically after entering the DOB on a Form? The "Current Date" is entered automatically as the "Updated" field when creating a new record.

Thanks,
Bill

 

[scriverb]

Here are a couple that do the job:
CLng(Date1 - Date2 / 365.2425) {'compliments of CCLINT}
or

IIf(DatePart("m",[tblYourTable]![BIRTH_DATE])=DatePart("m",Date()) And DatePart("d",[tblYourTable]![BIRTH_DATE])=DatePart("d",Date()), CInt((DateDiff('d',[tblYourTable]![BIRTH_DATE],Date())/365.25)), CInt((DateDiff('d',[tblYourTable]![BIRTH_DATE],Date())/365.25)-0.5))

You can perform a search and find numerous previous threads that address this issue also.

Bob Scriver

 

[MichaelRed]

There are NUMEROUS posts on this subject throughout these fors. Use the search capability use [DOB | Age | ... ] to return many additional resources.




MichaelRed
m.red@att.net

Searching for employment in all the wrong places

 

[gadsbywc]

Thanks, i may have had a dumb moment not to think of a search, but thanks for the reply.

 

[SeeThru]

Also, I wouldnt bother saving the Age in the table (unless you need to) but calculate it on the fly whenever you need it. Otherwise, as time goes on, the DoB will be correct, but the age will be wrong as they will have had a birthday.

HTH

 

[CCLINT]

>{'compliments of CCLINT}

There was a slight error in that suggestion which could lead to a wrong figure, for the simple fact that CLng rounds up.
I corrected it later in the thread where originally posted:

Correction from:

CLng(Date1 - Date2 / 365.2425)

To:

Int((Date1 - Date2 + 1) / 365.2425)

Please also note that Date2 needs to be the oldest date, or the birthdate.

So, to calculate one's age use:
Int((Date - dtBithDate + 1 ) / 365.2425)

The dates in this case must be stored in date variables and not strings.

If you store them in strings, then you will need to first convert the types:

Int((Date- CDate(strBithDate) + 1) / 365.2425)



So, a final solution would be to add the following into a module or form code:

Public Function Age(ByVal DOB As String) As Long
If Not IsDate(DOB) Then Exit Function
Age = Int((Date - CDate(DOB) + 1) / 365.2425)
End Function

and call it like:

txtDOB.Text = "05/02/49"
TxtAge.Text = Age(txtDOB.Text)

I have the DOB argument set as a string and passed ByVal.
This will allow you to pass a DOB as a string or as a Date type.
It also returns zero if the passed value cannot be converted to a date. You may want to raise an error here.

 

[scriverb]

CCLINT: My apologies for posting inaccurately with your reference. But, I still think you have a problem with your calculation. In the instance of a birthdate that is tomorrow your calculation is off by a year. I believe if you take out the +1 your calculation works fine.

Public Function Age(ByVal DOB As String) As Long
If Not IsDate(DOB) Then Exit Function
Age = Int((Date - CDate(DOB) + 1) / 365.2425)
End Function

Just take out the Red( +1) and it calculates correctly. It is just the instance of the DOB being one day greater than Date(). Correct me if I am wrong about this. I saw the difference when testing yours against the expression that I provided.




Bob Scriver

 

[CCLINT]

Please post an example DOB...

 

[MichaelRed]

To be boringly repetitious. This HAS been exhaustively discussed. Simply do the search and review. You can see NUMEROUS soloutions as well as the critiques. Some of which apply to the possabilities posted here in this thread.



MichaelRed
m.red@att.net

Searching for employment in all the wrong places

 

[scriverb]

05/03/1947 should return 55 but it returns 56. 05/02/2003 which is todays date returns 56 which it should. 05/04/2003 returns 55 which it should.

It is just the DOB +1 that returns the rollover of Age too soon.

Bob Scriver

 

[CCLINT]

>My apologies for posting inaccurately with your reference
Not your fault. I posted the correction much later.

So: removing the +1 will also not work always.
The math is correct, except for the first leap year. If the Year(DOB) is a leap year and the DOB is falls after 29 Feb, then 1 needs to be added.

Public Function Age(ByVal DOB As String) As Long
Dim iOffSet As Integer

If Not IsDate(DOB) Then Exit Function
iOffSet = Abs(IsDate("02/29/" & Year(DOB)) And Month(DOB) > 2)

Age = Int((CDate("02.05.03&quot - CDate(DOB) + iOffSet) / 365.2425)
End Function

I will test this further. And I have another possible solution which may be simpler (using the fact that 1899 is year 0)

 

[CCLINT]

Boring no.

Oh, I Know! plenty of methods to do this.
I know! the method using DateDiff and an offset, and I know! the method using 1899 as Year 0, and I know! plenty of other methods.

And I know that there is absolutly nothing wrong with trying to find alternatives to anything unless you do not mind the world standing still.

No one can say one method is the best...only maybe better than another.

 

[gadsbywc]

Thanks to all. This is more help than I expected. I know I should've used a search first but, I wonder if all this would have been found unless I asked.

Thanks again,
Bill

 

[sha76]

The best discussion & formula I've seen on this one is in one of the Crystal Reports forums - you'll probably be able to adapt the code, though I've not looked into it, but it's definitly worth reading for the clear logic.
You'll find it here: thread767-491770

 

[JimInKS]

Thought I might as well throw my version in here too. It is a bit of a brute force approach and has the caveat that if the person is less than 2 the age is reported in months.

Function age(DOB As Double) As Variant
' Given a birthday calculates the persons age.
' If person is less than 2 years old gives the age in months.
Dim CurDate As Double
Dim Years As Integer, Months As Integer
Dim M2 As Integer, M1 As Integer
Dim D2 As Integer, D1 As Integer

CurDate = Now
' Subtract the year part between the two dates
Years = DateDiff("yyyy", DOB, CurDate)

' Now fix up the age by checking if we had a birthday this year yet
M2 = DatePart("m", CurDate)
M1 = DatePart("m", DOB)
D2 = DatePart("d", CurDate)
D1 = DatePart("d", DOB)
If M2 < M1 Then Years = Years - 1
If M2 = M1 And D2 < D1 Then Years = Years - 1
If Years < 2 Then
Months = DateDiff(&quot;m&quot;, DOB, CurDate)
If D2 < D1 Then Months = Months - 1
age = Months & &quot;M&quot;
Else
age = Years
End If

End Function

 

[gadsbywc]

Thanks, JimInks, That is quite possibly the best help yet!