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!
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(
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?
SELECT P.ClientID, P.PlanId, P.PlanName, P.Participants, P.PlanStatus, Iif(P.DocTypeNo=1,"C",iif(
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?
Do more with
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
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(ob
'---Reapply the filter
GetSortedRecordset.Filter = varFilter
rsSource.Filter = varFilter
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(ob
'---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:
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]"
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.
"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.
Let's try casting the column:
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
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;
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
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.
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:
* 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;
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."
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.
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.
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 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.
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.
Premium Content
You need an Expert Office subscription to comment.Start Free Trial