Age from Dob

[dbl]

Hi I am using Access 97, I have downloaded the following code from MS web
site to work out the age of someone by entering there date of birth. When
you run the code in the immediate window it adds about 50 yrs to the age.

The months part of the code always returns "0"

Can anyone see where I am going wrong?

Option Explicit

'*************************************************************
' FUNCTION NAME: Age()
'
' PURPOSE:
' Calculates age in years from a specified 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 specified date.
' If the specified 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("m", StartDate, Now))
If (DatePart("d", StartDate) > DatePart("d", Now)) Then
tAge = tAge - 1
End If
If tAge < 0 Then
tAge = tAge + 1
End If

AgeMonths = CInt(tAge Mod 12)

End Function

Thanks for your help

Bob

 

[Guest]

lot of code.
try this instead

Sub dob()
Dim DateofBirth
DateofBirth = InputBox("Enter your date of birth")
MsgBox "You are " & DateDiff("yyyy", DateofBirth, Date)
& " years old."
End Sub

 

[Rick B]

Close, but that does not take into account if the birthdate has passed this
year.

Try it for 02-Jan-1968 and 02-Feb-1968 and you will get the same result.

As posted many many many many times in these newsgroups, the preffered way
to calculate a birthdate is...


DateDiff("yyyy",[Birthdate],Date())+(Format([Birthdate],"mmdd")>Format(Date(
),"mmdd"))

 

[Dirk Goldgar]

lot of code.
try this instead

Sub dob()
Dim DateofBirth
DateofBirth = InputBox("Enter your date of birth")
MsgBox "You are " & DateDiff("yyyy", DateofBirth, Date)
& " years old."
End Sub

Check this out in the Immediate window:

?DateDiff("yyyy", #12/31/2004#, #1/1/2005#)
1

 

[dbl]

Thanks for your help I am now sorted.

Bob

 

[Dan Knight]

Bob;
Try this formula:

Int((Date()-[BDate])/365.25)

instead of coding a UDF and it takes into account the issues that Dirk &
Rick pointed out.

Dan Knight

 

[Douglas J. Steele]

The formula Rick B gave it guaranteed to be accurate. What you're suggesting
may have errors (although admittedly it'll be correct most of the time)

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Bob;
Try this formula:

Int((Date()-[BDate])/365.25)

instead of coding a UDF and it takes into account the issues that Dirk &
Rick pointed out.

Dan Knight

Thanks for your help I am now sorted.

Bob

 

[dbl]

Hi the code I first posted I have now got to work and does give the correct
return in the cases I have tried, but is it possible for it to work out the
age from an incident date, i.e. how old were they when the incident took
place.

Thanks all for you input.

Bob

The formula Rick B gave it guaranteed to be accurate. What you're
suggesting may have errors (although admittedly it'll be correct most of
the time)

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Bob;
Try this formula:

Int((Date()-[BDate])/365.25)

instead of coding a UDF and it takes into account the issues that Dirk &
Rick pointed out.

Dan Knight

Thanks for your help I am now sorted.

Bob

Hi I am using Access 97, I have downloaded the following code from MS
web site to work out the age of someone by entering there date of
birth. When you run the code in the immediate window it adds about 50
yrs to the age.

The months part of the code always returns "0"

Can anyone see where I am going wrong?

Option Explicit

'*************************************************************
' FUNCTION NAME: Age()
'
' PURPOSE:
' Calculates age in years from a specified 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 specified
date.
' If the specified 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("m", StartDate, Now))
If (DatePart("d", StartDate) > DatePart("d", Now)) Then
tAge = tAge - 1
End If
If tAge < 0 Then
tAge = tAge + 1
End If
AgeMonths = CInt(tAge Mod 12)

End Function

Thanks for your help

Bob

 

[Douglas J. Steele]

Rick's code was:

DateDiff("yyyy",[Birthdate],Date())+((Format([Birthdate],"mmdd")>Format(Date
(),"mmdd"))

Replace the two references to Date() with the Incident date.

If the Incident Date is a field in the same table, try:

DateDiff("yyyy",[Birthdate],[IncidentDate])+((Format([Birthdate],"mmdd")>For
mat([IncidentDate],"mmdd"))

If Incident Date is a fix date, use something like:

DateDiff("yyyy",[Birthdate],#2005-01-13#)+((Format([Birthdate],"mmdd")>"0113
")



--
Doug Steele, Microsoft Access MVP

(No private e-mails, please)

Hi the code I first posted I have now got to work and does give the correct
return in the cases I have tried, but is it possible for it to work out the
age from an incident date, i.e. how old were they when the incident took
place.

Thanks all for you input.

Bob

The formula Rick B gave it guaranteed to be accurate. What you're
suggesting may have errors (although admittedly it'll be correct most of
the time)

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Bob;
Try this formula:

Int((Date()-[BDate])/365.25)

instead of coding a UDF and it takes into account the issues that Dirk &
Rick pointed out.

Dan Knight

:

Thanks for your help I am now sorted.

Bob

Hi I am using Access 97, I have downloaded the following code from MS
web site to work out the age of someone by entering there date of
birth. When you run the code in the immediate window it adds about 50
yrs to the age.

The months part of the code always returns "0"

Can anyone see where I am going wrong?

Option Explicit

'*************************************************************
' FUNCTION NAME: Age()
'
' PURPOSE:
' Calculates age in years from a specified 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 specified
date.
' If the specified 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("m", StartDate, Now))
If (DatePart("d", StartDate) > DatePart("d", Now)) Then
tAge = tAge - 1
End If
If tAge < 0 Then
tAge = tAge + 1
End If
AgeMonths = CInt(tAge Mod 12)

End Function

Thanks for your help

Bob

 

[dbl]

Thanks for all your help.

Bob

Rick's code was:

DateDiff("yyyy",[Birthdate],Date())+((Format([Birthdate],"mmdd")>Format(Date
(),"mmdd"))

Replace the two references to Date() with the Incident date.

If the Incident Date is a field in the same table, try:

DateDiff("yyyy",[Birthdate],[IncidentDate])+((Format([Birthdate],"mmdd")>For
mat([IncidentDate],"mmdd"))

If Incident Date is a fix date, use something like:

DateDiff("yyyy",[Birthdate],#2005-01-13#)+((Format([Birthdate],"mmdd")>"0113
")



--
Doug Steele, Microsoft Access MVP

(No private e-mails, please)

Hi the code I first posted I have now got to work and does give the correct
return in the cases I have tried, but is it possible for it to work out the
age from an incident date, i.e. how old were they when the incident took
place.

Thanks all for you input.

Bob

The formula Rick B gave it guaranteed to be accurate. What you're
suggesting may have errors (although admittedly it'll be correct most
of
the time)

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



Bob;
Try this formula:

Int((Date()-[BDate])/365.25)

instead of coding a UDF and it takes into account the issues that Dirk &
Rick pointed out.

Dan Knight

:

Thanks for your help I am now sorted.

Bob

Hi I am using Access 97, I have downloaded the following code from MS
web site to work out the age of someone by entering there date of
birth. When you run the code in the immediate window it adds about 50
yrs to the age.

The months part of the code always returns "0"

Can anyone see where I am going wrong?

Option Explicit

'*************************************************************
' FUNCTION NAME: Age()
'
' PURPOSE:
' Calculates age in years from a specified 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 specified
date.
' If the specified 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("m", StartDate, Now))
If (DatePart("d", StartDate) > DatePart("d", Now)) Then
tAge = tAge - 1
End If
If tAge < 0 Then
tAge = tAge + 1
End If
AgeMonths = CInt(tAge Mod 12)

End Function

Thanks for your help

Bob

 

[Rpatton]

Ok. i have that and that worked just fine, but how do e determine if for
example , billy is 12 1/2 the DateDiff returns 12.50000 is there a way to
make that 12 1/2?

Thanks for all your help.

Bob

Rick's code was:

DateDiff("yyyy",[Birthdate],Date())+((Format([Birthdate],"mmdd")>Format(Date
(),"mmdd"))

Replace the two references to Date() with the Incident date.

If the Incident Date is a field in the same table, try:

DateDiff("yyyy",[Birthdate],[IncidentDate])+((Format([Birthdate],"mmdd")>For
mat([IncidentDate],"mmdd"))

If Incident Date is a fix date, use something like:

DateDiff("yyyy",[Birthdate],#2005-01-13#)+((Format([Birthdate],"mmdd")>"0113
")



--
Doug Steele, Microsoft Access MVP

(No private e-mails, please)

Hi the code I first posted I have now got to work and does give the correct
return in the cases I have tried, but is it possible for it to work out the
age from an incident date, i.e. how old were they when the incident took
place.

Thanks all for you input.

Bob
The formula Rick B gave it guaranteed to be accurate. What you're
suggesting may have errors (although admittedly it'll be correct most
of
the time)

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



Bob;
Try this formula:

Int((Date()-[BDate])/365.25)

instead of coding a UDF and it takes into account the issues that Dirk &
Rick pointed out.

Dan Knight

:

Thanks for your help I am now sorted.

Bob

Hi I am using Access 97, I have downloaded the following code from MS
web site to work out the age of someone by entering there date of
birth. When you run the code in the immediate window it adds about 50
yrs to the age.

The months part of the code always returns "0"

Can anyone see where I am going wrong?

Option Explicit

'*************************************************************
' FUNCTION NAME: Age()
'
' PURPOSE:
' Calculates age in years from a specified 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 specified
date.
' If the specified 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("m", StartDate, Now))
If (DatePart("d", StartDate) > DatePart("d", Now)) Then
tAge = tAge - 1
End If
If tAge < 0 Then
tAge = tAge + 1
End If
AgeMonths = CInt(tAge Mod 12)

End Function

Thanks for your help

Bob

 

[Rick B]

Read that formula. It simply subtracts in YEARS. If you need more detail,
you'd have to write a more complex formula.

You could write a similar formula to calculate the number of months and use
the two numbers together. Other than that, you'd need to subtract and get
the number of DAYS then divide by 365 (or is it 366 with leap year) to get a
fractional number like you wanted. You'd have to round it or format the
number of digits if you wanted a fairly clean number.

Rick B

Ok. i have that and that worked just fine, but how do e determine if for
example , billy is 12 1/2 the DateDiff returns 12.50000 is there a way to
make that 12 1/2?

Thanks for all your help.

Bob

Rick's code was:

DateDiff("yyyy",[Birthdate],Date())+((Format([Birthdate],"mmdd")>Format(Date
(),"mmdd"))

Replace the two references to Date() with the Incident date.

If the Incident Date is a field in the same table, try:

DateDiff("yyyy",[Birthdate],[IncidentDate])+((Format([Birthdate],"mmdd")>For
mat([IncidentDate],"mmdd"))

If Incident Date is a fix date, use something like:

DateDiff("yyyy",[Birthdate],#2005-01-13#)+((Format([Birthdate],"mmdd")>"0113
")



--
Doug Steele, Microsoft Access MVP

(No private e-mails, please)


Hi the code I first posted I have now got to work and does give the
correct
return in the cases I have tried, but is it possible for it to work out
the
age from an incident date, i.e. how old were they when the incident took
place.

Thanks all for you input.

Bob
The formula Rick B gave it guaranteed to be accurate. What you're
suggesting may have errors (although admittedly it'll be correct most
of
the time)

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



Bob;
Try this formula:

Int((Date()-[BDate])/365.25)

instead of coding a UDF and it takes into account the issues that Dirk
&
Rick pointed out.

Dan Knight

:

Thanks for your help I am now sorted.

Bob

Hi I am using Access 97, I have downloaded the following code from
MS
web site to work out the age of someone by entering there date of
birth. When you run the code in the immediate window it adds about
50
yrs to the age.

The months part of the code always returns "0"

Can anyone see where I am going wrong?

Option Explicit

'*************************************************************
' FUNCTION NAME: Age()
'
' PURPOSE:
' Calculates age in years from a specified 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 specified
date.
' If the specified 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("m", StartDate, Now))
If (DatePart("d", StartDate) > DatePart("d", Now)) Then
tAge = tAge - 1
End If
If tAge < 0 Then
tAge = tAge + 1
End If
AgeMonths = CInt(tAge Mod 12)

End Function

Thanks for your help

Bob

 

[Rpatton]

Thanks Rick,
That does it!