Simple question...Calculating Age

  • Thread starter Thread starter Bayou BoB
  • Start date Start date
B

Bayou BoB

Hi;

Just a quick question. What is the equation for calculating age in a
query? I have a date of birth field, and want to calculate the
person's actual age, and if possible, a decimal place, and then
months...For example 37.6 would represent 37 years, 6 months old. The
current equation I just tried to use subtracted years and gives you a
simple age, but it's not always right given it doesn't factor in the
month a person was born in. Thanks in advance!

Kevin
 
Hi Kevin:

Here are my "age calculation" files- there are several ways of skinning this
horse-

AGE CALCULATION
-------------------------
METHOD 1:
Option Compare Database 'Use database order for string comparisons

Function Age(varDOB As Variant)
Dim varAge As Variant
If IsNull(varDOB) Then Age = 0: Exit Function
varAge = DateDiff("yyyy", varDOB, Now)
If Date < DateSerial(YEAR(Now), Month(varDOB), Day(varDOB)) Then
varAge = varAge - 1
End If
Age = varAge
End Function

I use the above as a module and use it to calculate someone's age-attained.

I wouldn't place it in a table as stored data, but the function could be
used anywhere else.

Copied from forum letter of: Simon Tetlow, London, UK

METHOD 2:(mm/dd/yy) and calculate (Age in Years) and (Age in months) in two separate
fields. <<

Here is another function that takes a DOB and calculates age in
Years, Months or Days dependent on the "patients" current age. You can
modify it as required to calculate your ages:

Function Age (ctlDOB As Control)
' Comments :
' Parameters : ctlDOB
' Returns :
' Created : 04/20/97 by Tracy Mock, HomeUse Software
' Modified : 04/20/97, 6:20:03 PM
'
' --------------------------------------------------------
On Error GoTo Age_Err
Dim intPtAge As Integer
Dim strAdjustedAge As String

intPtAge = DateDiff("yyyy", ctlDOB, Date) + (DatePart("y", ctlDOB) >
DatePart("y", Date))
If intPtAge < 2 Then
intPtAge = DateDiff("m", ctlDOB, Date) + (DatePart("y", ctlDOB) >
DatePart("y", Date))
strAdjustedAge = intPtAge & " months"
If intPtAge < 2 Then
intPtAge = DateDiff("d", ctlDOB, Date) + (DatePart("y", ctlDOB)
DatePart("y", Date))
Click to expand...
strAdjustedAge = intPtAge & " days"
End If
Else
strAdjustedAge = intPtAge & " years"
End If
Age = strAdjustedAge

Age_Exit:
Exit Function

Age_Err:
If Err = 94 Then
Resume Age_Exit
End If
MsgBox Err & " " & Error$, MB_ICONSTOP, App_Name
Resume Age_Exit

End Function

METHOD 3:
(The one I use. Unfortunately, it's doesn't do months, since I'm not a
pediatrician! It's the shortest, though.)
Add to the form's OnCurrent property (the [DOB] is the date of birth field)-

AgeCalcVariable = Int((Date()-[DOB])/365.25)
If AgeCalcVariable<0 Then
AgeVariable = AgeCalcVariable+100
Else
AgeVariable = AgeCalcVariable
End If

I call the last method the "lazy man's route to age calculations"! So pick
your flavor...

Regards,
Al
 
Back
Top