Sorting a query on part of a date.

  • Thread starter Thread starter Neil Grantham
  • Start date Start date
N

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
 
Neil

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