Calculating Age from Date of Birth 8

[JJOHNS]

I have a form which takes personal information. My user would like to have the Age of the person fill in automatically when the Date of Birth is entered. Any ideas on how to do this?

 

[CosmoKramer]

One way of calculating age (it may not be exact right around the person's birthdate) is to set up a text box on the form with this as its control source:

=(Int((Date()-[Date_of_Birth])/365))

 

[RegionsRob]

Cosmo's answer is great if you just want a down-n-dirty calculation. Here's a more precise calculation from MS Knowledge Base, Article Q100136

'==========================================================
' General Declaration
'==========================================================

Option Explicit

'*************************************************************
' FUNCTION NAME: Age()
'
' PURPOSE:
' Calculates age in years from a given date to today's date.
'
' INPUT PARAMETERS:
' StartDate: The beginning date (for example, a birth date).
'
' RETURN
' Age in years.
'*************************************************************

Function Age (varBirthDate As Variant) As Integer
Dim varAge As Variant

If IsNull(varBirthdate) then Age = 0: Exit Function

varAge = DateDiff("yyyy", varBirthDate, Now)
If Date < DateSerial(Year(Now), Month(varBirthDate), _
Day(varBirthDate)) Then
varAge = varAge - 1
End If
Age = CInt(varAge)
End Function

'*************************************************************
' FUNCTION NAME: AgeMonths()
'
' PURPOSE:
' Compliments the Age() function by calculating the number of months
' that have expired since the last month supplied by the given date.
' If the given date is a birthday, the function returns the number of
' months since the last birthday.
'
' INPUT PARAMETERS:
' StartDate: The beginning date (for example, a birthday).
'
' RETURN
' Months since the last birthday.
'*************************************************************
Function AgeMonths(ByVal StartDate As String) As Integer
Dim tAge As Double
tAge = (DateDiff(&quot;m&quot;, StartDate, Now))
If (DatePart(&quot;d&quot;, StartDate) > DatePart(&quot;d&quot;, Now)) Then
tAge = tAge - 1
End If

If tAge < 0 Then
tAge = tAge + 1
End If

AgeMonths = CInt(tAge Mod 12)

End Function

 

[Jitterbug]

Got to admit i prefer cosmos method. Lot easier. Normally you only need to know someones age to the nearest year, not to 10 decimal places.

 

[JJOHNS]

I appreciate the help. I used RegionsRob's answer, except I simplified it a little. If anyone's interested, here's how I changed it to suit my form:



txtAge = DateDiff(&quot;yyyy&quot;, txtDOB, Now)
If Date < DateSerial(Year(Now), Month(txtDOB), _
Day(txtDOB)) Then
txtAge = txtAge - 1
End If
txtAge = CInt(txtAge)

I entered this as an expression in the Events properties of my DOB text box on AfterUpdate. Works like a charm. Thanks a lot to both of you for your help.

 

[JerryDennison]

Here's a much easier method to implement (it can be done in a query without using code):

Age: DateDiff(&quot;yyyy&quot;, [DOB], Now()) + (Date() < DateSerial(Year(Now()), Month([DOB]), Day([DOB]))

The reason this works is the way Access returns True and False values. Technically, True is any value OTHER than zero (0) with False being zero (0). Access will return -1 to denote True and zero (0) to denote False. We can use this to then add a -1 to the DateDiff() function results if the current date is less than the individual's birthdate.

 

[Guest_imported]

Actually there are not 365 days in a year. Over a 4 year period there is a leap year.

((3*365)+366)/4 = 365.25

This
=(Int((Date()-[Date_of_Birth])/365))

should be
=(Int((Date()-[Date_of_Birth])/365.25))

 

[JJOHNS]

OK. The code I used before worked fine in the form, but I still had a little trouble using it in a report. It wouldn't work because the code would try to run before a value was entered in the DOB textfield. I would get a null value error. I used a query with Jerry's answer to solve that. Now I have the age calculated one way in the form and another way in the report. This site is great! I'm learning something new every day.

 

[jackgyger]

Same thing??? A woman's Medical Code is 4312 (for arthritis).. How to do formula where if Medical Code is 4312, print contents of Description field: better, worse, unchanged)??? Thanks.

 

[Wacko1971]

Hi, I am relatively new to Access (use to use Excel all the time).

I need the same thing, but am unsure as to how to get it to work.

Some ones birthday is 01/04/1971 entered in a table. Now on a form I want to display their age for viewing purposes only (it is a employees roster base)

This is what I have tried :

In the Control Source feild I entered

=(Int((Date()-[Birthdate])/365.25))

But it shows their age as being 30. Clearly their age is 32.

So this one is no go.

Where and how would I implement RegionsRob's code?

Or, is there an easier place to implement this as I am trying to have it displayed in a form.

Cheers

 

[CosmoKramer]

wacko1971,

Check out my response in the following thread:

thread702-411761