Help!! I need to write a query whi

[cantona]

Help!!
I need to write a query which will give me 'Age as at 31/08/2002'
I have the date of birth field, but im not sure how to write a query that will work out age as of the 31st August 2002.

Can anyone help?

 

[Dodgey]

SELECT DISTINCT Datediff('y', #31/08/2002#, myTable.Date_Of_Birth) AS AGE_AS_AT, ...

P.S I might have the dates to compare around the wrong way. It's been some time.

If this is helpful, please please mark this as helpful.

 

[cantona]

Hi, thanks for your suggestion, however it doesnt seem to work. This is what i have tried;

SELECT DISTINCT DateDiff('Y',[Pupil]![DOB],#8/31/2002#) AS Age_at, Pupil.Surname, forenames, Pupil.DOB
FROM Pupil;

For example, a person with DOB of 15/01/2000 returns 959?

If i swith the dob and #8/31/2002# around, it returns -959?

Any suggestions?

 

[huskerdon]

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(&quot;YYYY&quot;, 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(&quot;YYYY&quot;, DOB, On_Date)
Else
FindYears_onDate = DateDiff(&quot;YYYY&quot;, 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(&quot;YYYY&quot;, 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!