"SELECT DISTINCT and ORDER BY"-problem

Hi!

I can't seem to get my query to work. It returns (translated from Swedish):

Microsoft JET Database Engine (0x80004005)
The ORDER BY-instruction (myDate)..."doesn't work with"...DISTINCT.

This is my query (somewhat simplified):

set rs=Server.CreateObject("ADODB.recordset")
sql="SELECT DISTINCT DATEPART(year, myDate) AS 'myColumn_alias' FROM myDB"
sql=sql & " WHERE myName='" & myName & "'"
sql=sql & " ORDER BY myColumn_alias"
rs.Open sql,conn

Is it because I have a WHERE-clause? If so, how should I design my query (I need only the years to populate a "select from" dropdownbox)? Or is it something else?

ThanksI don't think you can refer to a column alias the samw way you refer to a column.

Try:

ORDER BY DATEPART(year, myDate)|||I've tried that, but then I get the message that too few parameters are given and that 1 is expected.

:(|||All I had to do was to use YEAR(myDate) instead of DATEPART(year, myDate). Now it works!