How sort recordsets with fallback tiebreak fields

[paul.schrum]

Access 2007 developing 2003 database
DAO

I have a recordset I want to sort in ascending order of field
"localOrder" (Long). Sometimes there will be ties. In such cases I
want it to decide on ties by sorting on UrgencyID (Long). If there
are still ties, I want it to sort by "whenAssigned", which is a Date
+Time field.

I tried the following code,

Set rst = CurrentDb().OpenRecordset( _
"SELECT taskID, whenAssigned, UrgencyID, localOrder FROM
tbl_tasks " & _
"WHERE personIDassignedTo = " & myID & _
" AND statusID = 2 ", dbOpenDynaset)

rst.Sort = "localOrder, UrgencyID, whenAssigned"
Set rstPtr = rst.Clone
rstPtr.MoveFirst

My code then proceeds to iterate through the recordset using rstPtr,
expecting it to be sorted as I need it, but it does not seem to be
happening.

Previously I had tried having several intermediate recordsets, as in

rst.Sort = "whenAssigned"
set rstTime = rst.clone
rstTime.sort = "UrgencyID"
set rstUrgency = rstTime.clone
rstUrgency.sort = "localOrder"
rstPtr = rstUrgency.clone
rstPtr.moveFirst

[.close and = Nothing code elided above]
This also did not work.

How can I accomplish the kind of sort I need?

- Paul
Schrum

 

[Allen Browne]

How about using an ORDER BY clause in the query:

strSql = "SELECT taskID, whenAssigned, UrgencyID, localOrder " & _
"FROM tbl_tasks " & _
"WHERE personIDassignedTo = " & myID & _
"AND statusID = 2 " & _
"ORDER BY localOrder, UrgencyID, whenAssigned;"

 

[paul.schrum]

Thanks Allen,

I should have tried that. Having not, I embarrassed myself.

It works as I need it to. Thanks for helping me.

- Paul

How about using an ORDER BY clause in the query:

strSql = "SELECT taskID, whenAssigned, UrgencyID, localOrder " & _
    "FROM tbl_tasks " & _
    "WHERE personIDassignedTo = " & myID & _
    "AND  statusID = 2  " & _
    "ORDER BY localOrder, UrgencyID, whenAssigned;"

--
Allen Browne - Microsoft MVP.  Perth, Western Australia
Tips for Access users -http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

Access 2007 developing 2003 database
DAO

I have a recordset I want to sort in ascending order of field
"localOrder" (Long).  Sometimes there will be ties.  In such cases I
want it to decide on ties by sorting on UrgencyID (Long).  If there
are still ties, I want it to sort by "whenAssigned", which is a Date
+Time field.

I tried the following code,

   Set rst = CurrentDb().OpenRecordset( _
       "SELECT taskID, whenAssigned, UrgencyID, localOrder FROM
tbl_tasks " & _
       "WHERE personIDassignedTo = " & myID & _
       " AND  statusID = 2 ", dbOpenDynaset)

   rst.Sort = "localOrder, UrgencyID, whenAssigned"
   Set rstPtr = rst.Clone
   rstPtr.MoveFirst

My code then proceeds to iterate through the recordset using rstPtr,
expecting it to be sorted as I need it, but it does not seem to be
happening.

Previously I had tried having several intermediate recordsets, as in

rst.Sort = "whenAssigned"
set rstTime = rst.clone
rstTime.sort = "UrgencyID"
set rstUrgency = rstTime.clone
rstUrgency.sort = "localOrder"
rstPtr = rstUrgency.clone
rstPtr.moveFirst

[.close and = Nothing code elided above]
This also did not work.

How can I accomplish the kind of sort I need?

- Paul
Schrum- Hide quoted text -

- Show quoted text -