Sort property doesn't seem to work on composite fields (ADO)

Using an ADO Recordset, I opened a query in the following manner:

SELECT Units.UnitID, Units.StreetName & ' ' & Units.StreetNumber as Address FROM Units

Then, when I tried to set the Sort Property to Address:

rstUnits.Sort = "Address"

I got a runtime error "Order cannot be opened", which doesn't appear anywhere on the internet or MSDN.

I _did_ remember to set the CursorLocation property to adUseClient, and it _does_ work if I set the Sort property to "UnitID".

I didn't see documentation anywhere that states that only real databse fields can appear in the Sort property.

Any insight on the matter will be appreciated.
Thanks.

Israel

Hi, i may not be able to suggest u a solution but i wonder if u can use 'alias' names to sort records. i would very much like to know the solution please. all the best

[Clunietp]

This code works fine for me....I am using SQL Server 7 with ADO 2.5


Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset

Set cn = New Connection

cn.Open "Provider=SQLOLEDB;Data Source=W2kSrv;Integrated Security=SSPI;Database=Northwind"

Set rs = New Recordset

rs.CursorLocation = adUseClient
rs.Open "Select CustomerID, Address + ' ' + City as TheNewAddress from Customers", cn, adOpenStatic, adLockReadOnly

rs.Sort = "TheNewAddress"

Do Until rs.EOF = True
Debug.Print rs(0) & " | " & rs(1)
rs.MoveNext
Loop

The same code (modified to open an Access 2000 mdb file) generated the error I was referring to.

I got "Order Cannot Be Opened" on the line that sets the Sort property.

So either there's somethig wrong with my computer, or it doesn't work on Access 2000.
Since I'm pretty sure it used to work a while ago, I'm guessing something messed up my ADO (It's 2.5, just like yours).

Thank you for your answer.
Israel.