HELP On Sorting Recordsets !!!!!!!!!!!!!!

[TrekFan]

Hi there,

I have a recordset with data (yes with data ! J ) .. 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


It gets 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

 

[tedsmith]

I think you would have to create a temporary table in vb with the money and date fields formatted to date and currency and the sorting order set the way you want it.
You can do this all in code.
Then dump the database data into this temporary table.
Use a recordset loop with
Begintrans
Do until OldTable.EOF
NewTable.Addnew
NewTable!NewDate = CVDATE(OldTable!DateString)
NewTable!NewMoney=CCUR(Oldtable!MoneyString)
NewTable.Update
Oldtable.Movenext
Loop
Committrans

The new table will then be in the order you want.

You might want an error trap in case of rubbish in your data