Access .sortfield not working

how are you using the strField variable?

rs.Sort = strField
           
            With Me
                .lblRecordCount.Caption = ""
                 Set .lstPlanList.Recordset = GetSortedRecordset(rs, rs.Sort)

Then I call this function

Public Function GetSortedRecordset(ByRef rsSource As adodb.Recordset, Optional ByVal strSort As String) As adodb.Recordset
    Dim varFilter As Variant
    Dim objStream As adodb.Stream

        On Error GoTo ErrHandler
   
            '---Create a new ADODB stream object
                Set objStream = New adodb.Stream
               
            '---Save the filter into a variable for later use.
                varFilter = rsSource.Filter
               
            '---Remove the filter from the recordset.
                rsSource.Filter = adodb.adFilterNone
               
            '---Apply the sort to the recordset.
                rsSource.Sort = strSort
               
            '---Save the sorted recordset to an ADODB stream.
                Call rsSource.Save(objStream, adPersistADTG)
               
            '---Create a new recordset from the stream object
                Set GetSortedRecordset = New adodb.Recordset
                Call GetSortedRecordset.Open(objStream)
               
            '---Reapply the filter
                GetSortedRecordset.Filter = varFilter
                rsSource.Filter = varFilter

I just realized that you are trying to alias a field (P.DocTypeNo) with its own name.

Try this:

SELECT P.ClientID, P.PlanId, P.PlanName, P.Participants, P.PlanStatus, 
Iif(P.DocTypeNo=1,"C",iif(P.DocTypeNo=2,"P","V")) as DocType
FROM tbl_Plans AS P;


                 Case 6
                        strField = "[DocType]"

Select allOpen in new window

I did that already and received the same error. In my original post I said:
"At first I thought it was do to the alias AS DocTypeNo so I changed it to DocType in the query and in Access but I still get the same error"

I made sure I changed it on the query and on the code on the Access form.

oops.  Sorry, I forgot that text by the time I started looking at the code.

Let's try casting the column:

SELECT P.ClientID, P.PlanId, P.PlanName, P.Participants, P.PlanStatus, 
CStr(Iif(P.DocTypeNo=1,"C",iif(P.DocTypeNo=2,"P","V"))) as DocType
FROM tbl_Plans AS P;

Select allOpen in new window

I would have suggested looking at the recordset type, but you said this worked in another environment.

This is an old thread that discusses this problem at length, albeit in a different run-time environment.
https://www.experts-exchange.com/questions/20376559/Sorting-data-from-a-recordset-by-using-Sort-property.html

I tried casting as you suggested. I received the same error. I'm tempted to tell the DBA to add an additional field for the Text "C","P","V" but the sort order is important. Right now 1,2,3 equates to "C","P","V" but what happens if they add a 4th and it's an "A".  
I tested that is was calling the query correctly. In the Access form code i changed it to DocTypeX and it said the Item could not be found in the collection. So I know my query works and it is returning DocType (DocTypeNo). And all the other sort fields I pass thru are working. Based on what the user picks it sorts by ClientID or PlanID or Participants etc.
I didn't think it would be this difficult.
I read the thread and it talks about types but I'm at a loss on what Type I should try to cast it as.

* Does the [DocTypeNo] column contain any Null values?
* Let's try the Choose function in place of the IIF() function:

SELECT P.ClientID, P.PlanId, P.PlanName, P.Participants, P.PlanStatus, 
CStr(Choose(P.DocTypeNo,"C","P","V")) as DocType
FROM tbl_Plans AS P;

Select allOpen in new window

While that may work, it does suffer from the same problem as the IIF() if the number of possible values in the [DocTypeNo] field changes.

I verified before I ran the query there are no Null values. In fact the default when the field is populated from the begining and going forward is 2 which equates to a "P".

For your Choose query when I run the Query alone it works fine. When I run it on the Access form (for any sort field) I get "Data provider or other service returned an E_Fail".

On the thread you provided it talks about the different data formats. But I'm not sure what to do except to test each type of conversion functions. I even tried changing the DocTypeNo on the table to a Text field and it still gave me the same error 'Sort order cannot be applied."

At the bottom of the query run in the Access form I inserted
    Debug.Print GetPlanList.Fields(4)
    Debug.Print GetPlanList.Fields(5)
And they returned the correct values "Active" for index 4 and "C" for Index 5.
I can't seem to remember the property that will let me see the column (field name) that is returned from them. I.E for index 4 it's PlanStatus, index 5 is DocType. I figure if I could see what was in the internals I could see what field name was messing up the sort.

I remember it.
                Debug.Print GetPlanList.Fields(4).Name
                Debug.Print GetPlanList.Fields(5).Name

Index 4 returns     PlanStatus
Index 5 returns     DocType

If this is true I'm at a loss for why I'm getting the sort error.

What is the open type of the recordset?

What exactly do you mean? We use ADO.
The queries and tables are on a back end Access database and the forms and all the calls to open the Database are on the Front End.
I'm working on many projects and we're not allowed to work from home or bring the code off site. Let me know exactly what you need and I can get to it in the morning.
Thanks.

dynaset, snapshot, forward-only, etc

Open type is Dynaset

I selected my own work around