Calculating Age in Access 2002

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I need to calculate a student’s age based upon the end of the month in which a test is given. In other words, if a student was born on 1/1/1986 and a test is given on 6/17/2004, will the student be 18 by 6/30/2004? Also, which function can I use to determine the end of the month if, for instance, [test_date]=6/17/04

TIA

Allison
 
Use the following functions:

Function GetAge(BirthDate As Date, Optional AsOfDate As Date = 0) As Integer

On Error Resume Next

'See if an arg was passed for end date, if not, use today's date
If AsOfDate = 0 Then
AsOfDate = Date
End If

'Get the number of years difference between the birthdate and the
enddate
GetAge = DateDiff("yyyy", BirthDate, AsOfDate)

'If the birthdate is not passed the asofdate in the calendar, we need to
subtract a year
GetAge = GetAge + (AsOfDate < DateSerial(Year(AsOfDate),
Month(BirthDate), Day(BirthDate)))

End Function

Function LastDayInMonth(Optional DateArg As Date = 0) As Date
' Return the last day in the specified month.

If DateArg = 0 Then
' Did the caller pass in a date? If not, use
' the current date.
DateArg = Date
End If

LastDayInMonth = DateSerial(Year(DateArg), Month(DateArg) + 1, 0)

End Function




--
Paul Overway
Logico Solutions, LLC
www.logico-solutions.com


Al. said:
I need to calculate a student's age based upon the end of the month in
Click to expand...
which a test is given. In other words, if a student was born on 1/1/1986
and a test is given on 6/17/2004, will the student be 18 by 6/30/2004?
Also, which function can I use to determine the end of the month if, for
instance, [test_date]=6/17/04?
 
Thank you, Paul, for your prompt response; however, I have little experience with SQL statements. How best can I put this into the Expression Builder

Your help is very much appreciated

Allison
 
Allison,

Try this...
Age:
DateDiff("yyyy",[DOB],DateSerial(Year([test_date]),Month([test_date])+1,0))+(Format([DOB],"mmdd")>Format(DateSerial(Year([test_date]),Month([test_date])+1,0),"mmdd"))

- Steve Schapel, Microsoft Access MVP
 
Back
Top