472,129 Members | 1,702 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 472,129 software developers and data experts.

Sorting a query on part of a date.

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
Nov 12 '05 #1
1 2838
ne****@btinternet.com (Neil Grantham) wrote in
news:c6*************************@posting.google.co m:
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

Remove the group by stuff from the query. Open your report in
design mode. click on the "sorting and grouping" icon in the
toolbar. Enter the fields you want ot group and sort by.

Save the report and test.

Bob Q

Nov 12 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

1 post views Thread by Neil Grantham | last post: by
5 posts views Thread by Christoph L. Kaufmann | last post: by
5 posts views Thread by Randy | last post: by
3 posts views Thread by Don | last post: by
8 posts views Thread by sara | last post: by
reply views Thread by leo001 | last post: by

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.