HELP On Sorting Recordsets !!!!

[TrekFan]

Hi there,

I have a recordset with data (yes with data ! ) .. and I need to be able to sort it by different columns (without requeryng the db). I though the “sort” property of the ADO 2.5 recordset would help me, but here is where it gets tricky: in case of “date columns” or “money fields” the existing SQL Server 2000 store procedures return this data formatted and converted to string …. So if I use a regular

Recordset.Sort “ColumnName” Order


I get really weird results … so I need to do some manipulation to strip out the characters to be able to sort them correctly. If I could do something like :

Recordset.Sort SubString(ColumnName,1,2) + SubString(ColumnName,4,2) “Order”



Any suggestions would be greatly welcome !!!!!!!!!


Thanks

TF

 

[MarkSweetland]

The easiest would be to convert the columns back into the correct format for your sort. Here's a brute force method to place items in a local recordset and sort accordingly; you'll have to adapt it to your specific needs:

Private Sub Form_Load()
Dim rs As New ADODB.Recordset

rs.Fields.Append "d", adInteger
rs.Fields.Append "m", adInteger
rs.Fields.Append "y", adInteger

rs.Open

For i = 1 To 15
rs.AddNew
rs("d&quot = i
rs("m&quot = 4
rs("y&quot = 3
rs.Update
Next
rs.MoveFirst
rs.Sort = "d asc" 'ascending
rs.MoveFirst
While Not rs.EOF
Debug.Print Format(rs("m&quot, "00&quot & "/" & Format(rs("d&quot, "00&quot & "/" & Format(rs("y&quot, "00&quot
rs.MoveNext
Wend
rs.Sort = "m asc, d desc" ' descending
rs.MoveFirst
While Not rs.EOF
Debug.Print Format(rs("m&quot, "00&quot & "/" & Format(rs("d&quot, "00&quot & "/" & Format(rs("y&quot, "00&quot
rs.MoveNext
Wend
rs.Close
set rs = nothing
End Sub





Mark