You can make your own function, and then use that in your query. Save the function in a Module. Here is an example:
Public Function FindYears_onDate(DOB As Date, On_Date As Date) As Long
Select Case Month(DOB)
' If the month of DOB is before the given month
Case Is < Month(On_Date)
FindYears_onDate = DateDiff("YYYY", DOB, On_Date)
' If the month of DOB is the given month, then check for the actual day of the month
' If the day is on OR before the given day of the month, then get the actual year difference
' If the day is after the given day of the month, then it's not a full year extra, so subtract 1 year
Case Is = Month(On_Date)
If Day(DOB) <= Day(On_Date) Then
FindYears_onDate = DateDiff("YYYY", DOB, On_Date)
Else
FindYears_onDate = DateDiff("YYYY", DOB, On_Date) - 1
End If
' If the month of DOB is after the given month, then it's not a full year extra, so subtract 1 year
Case Is > Month(On_Date)
FindYears_onDate = DateDiff("YYYY", DOB, On_Date) - 1
Case Else
' If less than 1 complete year
FindYears_onDate = 0
End Select
End Function
Your query would look like this:
SELECT DISTINCT FindYears_onDate([Pupil]![DOB], #8/31/2002#) AS ...
The function uses two parameters. The first is the DOB from the table, and the second (#8/31/2002#) can be manually entered in the SQL, or retrieved from a table. Remember to surround the date with #, since they are dates.
Good luck!