Avatar of Chuck Lowe
Chuck Lowe
 asked on

Access .sortfield not working

I'm using Access 2010. I'm calling a query.

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

Based on selections I add a sort field to the query

                    Case 2
                        strField = "[PlanID]"
                    Case 3
                        strField = "[PlanName]"
                    Case 4
                        strField = "[Participants]"
                    Case 5
                        strField = "[PlanStatus]"
                    Case 6
                        strField = "[DocTypeNo]"
                End Select

My issue is with DocTypeNo. When I pass it DocTypeNo I get an Error. Sort order cannot be applied. All other sort work fine.
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 even tried encasing the entire evaluation in () but I still get the error. Running the query outside of the access form works fine.

Any Ideas?
Microsoft Access

Avatar of undefined
Last Comment
Chuck Lowe

8/22/2022 - Mon
aikimark

how are you using the strField variable?
Chuck Lowe

ASKER
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
aikimark

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]"

Open in new window

Your help has saved me hundreds of hours of internet surfing.
fblack61
Chuck Lowe

ASKER
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.
aikimark

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

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;

Open 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
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Chuck Lowe

ASKER
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.
aikimark

* 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;

Open 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.
Chuck Lowe

ASKER
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."
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy
Chuck Lowe

ASKER
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.
Chuck Lowe

ASKER
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.
aikimark

What is the open type of the recordset?
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Chuck Lowe

ASKER
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.
aikimark

dynaset, snapshot, forward-only, etc
Chuck Lowe

ASKER
Open type is Dynaset
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes
ASKER CERTIFIED SOLUTION
Chuck Lowe

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
Chuck Lowe

ASKER
I selected my own work around