Sort on a calculated field

[ripper]

When I run this query, I am presented with a parameter input box asking for
tblGrievances.Year (unless I remove it from the ORDER BY clause). How can I
sort on the calculated field and the GrievNumber field?

Many thanks,

Rip

SELECT tblGrievances.*, DatePart("yyyy", tblGrievances.Yr) AS Year
FROM tblGrievances
ORDER BY tblGrievances.Year, tblGrievances.GrievNumber

 

[John Spencer]

SELECT tblGrievances.*
, Year(tblGrievances.Yr) AS [Year]
FROM tblGrievances
ORDER BY Year(tblGrievances.Yr), tblGrievances.GrievNumber

Year is a bad name for a column. Year is a function that returns the year
number of a date. And you cannot refer to an alias for a column in the ORDER
BY or WHERE clause - you must reiterate the calculation or refer to the
original field name


John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County