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?
Chuck LoweAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

how are you using the strField variable?
Chuck LoweAuthor Commented:
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]"

Open in new window

Protecting & Securing Your Critical Data

Considering 93 percent of companies file for bankruptcy within 12 months of a disaster that blocked access to their data for 10 days or more, planning for the worst is just smart business. Learn how Acronis Backup integrates security at every stage

Chuck LoweAuthor Commented:
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;

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.
Chuck LoweAuthor Commented:
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;

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 LoweAuthor Commented:
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."
Chuck LoweAuthor Commented:
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 LoweAuthor Commented:
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?
Chuck LoweAuthor Commented:
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.
dynaset, snapshot, forward-only, etc
Chuck LoweAuthor Commented:
Open type is Dynaset
Chuck LoweAuthor Commented:
I decided to do a work around. In my query I have
SELECT P.ClientID, P.PlanId, P.PlanName, P.Participants, P.PlanStatus, Iif(P.DocTypeNo=1,"C",iif(P.DocTypeNo=2,"P","V")) AS DocType,P.DocTypeNo
and I will display DocType but sort on DocTypeNo. It is working.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Chuck LoweAuthor Commented:
I selected my own work around
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.