Date calculations

  • Thread starter Thread starter Graham Naylor
  • Start date Start date
G

Graham Naylor

Hi,

I have a small application in which a person's date of birth is held on file
in a field which is set as date/time. I would like to display the person's
age (years and months) on a form. I assume the calculation would be in the
form's OnCurrent procedure.

Please could someone help out by showing me the code I need as I am stuck!

Thanks

Graham
 
If all that you want to do with it is display it, then
make another text box using (date()-[bday])/365.25 in
whole number with no decimal this will display age in
years taking into consideration leap year. You would have
to figure out the equation for months after the year.
 
Here's what you can use:

Public Function PersonAge(BirthDate as Date) As String
'This function returns the person's age in the format of:
'<Days>;<Months>;<Years>, which is in String Format.
Dim lngBY as Long, lngBM as Long, lngBD as Long
Dim lngCY as Long, lngCM as Long, lngCD as Long
lngCY = Year(Date)
lngCM = Month(Date)
lngCD = Day(Date)
lngBY = Year(BirthDate)
lngBM = Month(BirthDate)
lngBD = Day(BirthDate)
'Calculate Days Old
If CD < BD Then
If CM = 1 Then
CY = CY - 1
CM = 12
Else
CM = CM - 1
End If
PersonAge = CStr(Date - DateSerial(CY,CM,BD))
Else
PersonAge = CStr(CD-BD)
End If
'Calculate Months Old
If CM<BM Then
PersonAge = PersonAge & ";" & CStr(12 - BM + CM)
CY = CY - 1
Else
PersonAge = PersonAge & ";" & CStr(CM - BM)
End If
Calculate Years Old
If CY < BY Then
'Opps, Current Date hasn't reached BirthDate
PersonAge = ""
Else
PersonAge = PersonAge & ";" & CStr(CY-BY)
End If
End Function
 
Hi;

Thanks for the code it looks just like what I need, one small problem, when
I call the Function with the Birthdate set as the field name from file (type
set as date/time (Long date format) or as a variant set as date then I get
the return string as 0;0;0.

What am I doing wrong?

Thanks

Graham
 
Opps, didn't put the 'lng' as part of the variable names, my mistake there.
Try this:

Public Function PersonAge(Birthdate As Date) As String
'This function returns the person's age in the format of:
'<Days>;<Months>;<Years>, which is in String Format.
Dim lngBY As Long, lngBM As Long, lngBD As Long
Dim lngCY As Long, lngCM As Long, lngCD As Long
lngCY = Year(Date)
lngCM = Month(Date)
lngCD = Day(Date)
lngBY = Year(Birthdate)
lngBM = Month(Birthdate)
lngBD = Day(Birthdate)
'Calculate Days Old
If lngCD < lngBD Then
If lngCM = 1 Then
lngCY = lngCY - 1
lngCM = 12
Else
lngCM = lngCM - 1
End If
PersonAge = CStr(Date - DateSerial(lngCY, lngCM, lngBD))
Else
PersonAge = CStr(lngCD - lngBD)
End If
'Calculate Months Old
If lngCM < lngBM Then
PersonAge = PersonAge & ";" & CStr(12 - lngBM + lngCM)
lngCY = lngCY - 1
Else
PersonAge = PersonAge & ";" & CStr(lngCM - lngBM)
End If
'Calculate Years Old
If lngCY < lngBY Then
'Opps, Current Date hasn't reached BirthDate
PersonAge = ""
Else
PersonAge = PersonAge & ";" & CStr(lngCY - lngBY)
End If
End Function
 
Thanks, after a good sleep I saw the problem and I was about to post an
answer myself, thanks for the help though it got me going again with with
the application.

By the way, I like the solution.

Regards

Graham
 
No user defined function is needed to correctly calculate age.

If you want to calculate the age in a query ... create a "Fiel
Expression":

Age
DateDiff("yyyy",[DOB],Date())+(Date()<DateSerial(Year(Date()),Month([DOB]),Day([DOB])))


If you want to calculate the age directly in the Control Source of
txtbox on a Form or Report ... use:


DateDiff("yyyy",[DOB],Date())+(Date()<DateSerial(Year(Date()),Month([DOB]),Day([DOB])))

"DOB" should be the name of the field that contains the "Date o
Birth".

You can find many "user defined" functions to calculate "age" ... bu
there is no need to create and use a user defined function as th
expressions above will give you the correct result you want.

HTH
RD
 
But the problem with datediff, it returns the number of periods between the
2 dates, which is not what Graham was looking for. Example:

DateDiff("YYYY",DateSerial(1980,12,31),DateSerial(2003,9,18))

This results in the answer of 23, not 22 as Graham would be expecting. That
is cause 2003 - 1980 = 23

--
Ronald R. Dodge, Jr.
Production Statistician
Master MOUS 2000

R_Hicks said:
No user defined function is needed to correctly calculate age.

If you want to calculate the age in a query ... create a "Field
Expression":

Age:
DateDiff("yyyy",[DOB],Date())+(Date()<DateSerial(Year(Date()),Month([DOB]),D
ay([DOB])))


If you want to calculate the age directly in the Control Source of a
txtbox on a Form or Report ... use:

=
DateDiff("yyyy",[DOB],Date())+(Date()<DateSerial(Year(Date()),Month([DOB]),D
ay([DOB])))

"DOB" should be the name of the field that contains the "Date of
Birth".

You can find many "user defined" functions to calculate "age" ... but
there is no need to create and use a user defined function as the
expressions above will give you the correct result you want.

HTH
RDH
 
Glad to be of help. I appologize for the slight confusion with regards to
the variable names, but none the less, it was caught and corrected.
 
Back
Top