>>This should be quite simple but I can't find how to make that work. I am trying to group by on an expression field such as:
>>
>>

>>? SQLExec(lnHandle,"SELECT YEAR(AddDate) AS Test FROM News GROUP BY Test")
>>

>>
>>But, the only GROUP BY clause I can use is on a existing field. How can I GROUP BY on an expression?
>
>Ok, we have to reference with the entire syntax such as:
>
>

>? SQLExec(lnHandle,"SELECT YEAR(AddDate) AS Test FROM News GROUP BY YEAR(AddDate)")
>

>
>Thanks anyway

Michel,

There's a workaround that could be handy when expression is big or complicated and you don't want to repeat it in GROUP BY. It involves using derived table. For example,

lcSql = "SELECT test FROM (SELECT YEAR(AddDate) AS Test FROM News) GROUP BY test"
? SQLExec(lnHandle,lcSql)

Recent article in SQL Server magazine showed that this select generates the same execution plan as yours.