Access .sortfield not working

Chuck Lowe
Chuck Lowe used Ask the Experts™
on
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?
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Top Expert 2014

Commented:
how are you using the strField variable?

Author

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
Top Expert 2014

Commented:
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

Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Author

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.
Top Expert 2014

Commented:
oops.  Sorry, I forgot that text by the time I started looking at the code.
Top Expert 2014

Commented:
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.
http://www.experts-exchange.com/Programming/Languages/Scripting/ASP/Q_20376559.html

Author

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.
Top Expert 2014

Commented:
* 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.

Author

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

Author

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.

Author

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.
Top Expert 2014

Commented:
What is the open type of the recordset?

Author

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.
Thanks.
Top Expert 2014

Commented:
dynaset, snapshot, forward-only, etc

Author

Commented:
Open type is Dynaset
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.

Author

Commented:
I selected my own work around

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial