Prev: Parameter Queries in Crosstab Query
Next: Total Number of fields in a query
From: ripper on 14 Apr 2010 19:47
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


From: John Spencer on 14 Apr 2010 20:49
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

ripper wrote:
> 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
>
>
 | 
Pages: 1
Prev: Parameter Queries in Crosstab Query
Next: Total Number of fields in a query