Sorting a query on part of a date.

[Neil Grantham]

Hi

I have a Report based on a query, whose code looks like this:
SELECT Nursery.ChildID, Nursery.[Child Forenames], Nursery.[Child
Surname], Nursery.[Child Familiar Name], Nursery.ChildDOB,
GetIntakeTerm([ChildDOB]) AS IntakeTerm
FROM Nursery
GROUP BY Nursery.ChildID, Nursery.[Child Forenames], Nursery.[Child
Surname], Nursery.[Child Familiar Name], Nursery.ChildDOB,
GetIntakeTerm([ChildDOB]);

The GetIntakeTerm is a Module that calculates which Term a child falls
in to relating to their date of Birth and looks like this:
Function GetIntakeTerm(ChildDOB) As String
Select Case Month(ChildDOB)
Case 1 To 3
GetIntakeTerm = "Spring"
Case 4 To 9
GetIntakeTerm = "Summer"
Case 10 To 12
GetIntakeTerm = "Autumn"
End Select
End Function

I would really like the report to sort on both the Year and Term, so
that it will be a year on year report of children attending a nursery
and the Term they start in.

I have looked at DatePart, but would really appreciate some
suggestions on how to modify the code to achieve my goal.

Thanks in advance
Neil

 

[Jeff Boyce]

Neil

One approach might be to GROUP BY Year([ChildDOB]) before
GetIntakeTerm([ChildDOB]).