select distinct from jet to t-sql gives error #145

[Pampers]

Hi Folks,
I upsized my database tables to MsSql2000. Works ok. Except for a subform with a Select Distinct in it. It gives the error message: "Server: Msg 145, ORDER BY items must appear in the select list if SELECT DISTINCT is specified." But there is no Order By clause in the query. The query runs ok (in dataview), but when I open the subform which has its control source set to the query, the error message appears. The [order by] and [order by on]-properties of the subform are resp Null and off. Any ideas who to correct this?

This is the query:

SELECT DISTINCT tblOperations.operationsID, tblOperations.FileNumber, tblOperations.ETC, tblOperations.ETCDate, tblOperations.ETCTime, tblOperations.ETD, tblOperations.ETDDate, tblOperations.ETDTime, tblOperations.ETDRemark, tblOperations.[Next Port], tblOperations.[ETA next port d], tblOperations.[ETA next port t]
FROM tblOperations;

Pampers
Just back from holiday...
that may it explain it.

 

[Remou]

This is very much an uninformed guess, is there an OrderBy on the subform itself? That is, the Order By property?

 

[Pampers]

Hi Remou,
Yes there is.

The [order by] and [order by on]-properties of the subform are resp Null and off.

Pampers
Just back from holiday...
that may it explain it.

 

[Pampers]

By the way, I think it would be better to move my question to the Microsoft MsSql forum...

Pampers
Just back from holiday...
that may it explain it.