Thread: Calculate Age

I need to calculate the age of the student based on the [Date of Admission]

I have two fields

Field 1 [Date of Birth] - 11/10/1987
Field 2 [Date of Admission] 09/09/2009

I have CDate() both fields,

How do I create a formula to calculate the student’s age?

Hi,

Use the DateDiff Function

DateDiff Function

Returns a Variant (Long) specifying the number of time intervals between two specified dates.

Syntax

DateDiff(interval, date1, date2[, firstdayofweek[, firstweekofyear]])

The DateDiff function syntax has these named arguments:

Part Description
interval Required. String expression that is the interval of time you use to calculate the difference between date1 and date2.
date1, date2 Required; Variant (Date). Two dates you want to use in the calculation.
firstdayofweek Optional. A constant that specifies the first day of the week. If not specified, Sunday is assumed.
firstweekofyear Optional. A constant that specifies the first week of the year. If not specified, the first week is assumed to be the week in which January 1 occurs.

Note ~ For date1 and date2, if the Calendar property setting is Gregorian, the supplied date must be Gregorian. If the calendar is Hijri, the supplied date must be Hijri.

Example:

AdmissionAge: DateDiff("yyyy",[Date of Birth],[Date of Admission])

Cheers,

Hi,

Your table is storing the dates as "text", so you'll need to convert them to date format for the DATEDIFF function to work.

Try this query:

Code:

SELECT StudentAge.DOB, DateValue(Mid([DOB],1,4) & "-" & Mid([DOB],5,2) & "-" & Mid([DOB],7,2)) AS BirthDate, StudentAge.DTSESSION, DateValue(Mid([DTSESSION],1,4) & "-" & Mid([DTSESSION],5,2) & "-" & Mid([DTSESSION],7,2)) AS SessionDate, DateDiff("yyyy",[BirthDate],[SessionDate]) AS AdmissionAge
FROM StudentAge;

Cheers,