Most efficient way of Sorting Numbers (MS Access 97)

[BenjaminLim]

I have series of 4 digit numbers : 2341, 4239, 1254 in a table & I need to re-arrange them into 1234, 2349 and 1245 stored into another table.<br><br>What is the most efficient way to do so & what is the synthax like?<br><br>Pls help.&nbsp;&nbsp;Thanks.<br><br>NB : Pls send answers to following email address.<br><br>Regards<br>Benjamin<br>email : <A HREF="mailto:bl22447@glaxowellcome.co.uk">bl22447@glaxowellcome.co.uk</A>

 

[DougP]

Well you have to examine each number and compare it to all of the numbers<br><br>------------------------ Its a little clunky but it works<br>Public Function ReOrder1(NumberToCheck)<br>&nbsp;&nbsp;&nbsp;&nbsp;Dim A, NewNum As String<br>&nbsp;&nbsp;&nbsp;&nbsp;Dim db As Database, rst As Recordset<br>&nbsp;&nbsp;&nbsp;&nbsp;Set db = CurrentDb<br>&nbsp;&nbsp;&nbsp;&nbsp;Set rst = db.OpenRecordset(&quot;Test&quot<br>&nbsp;&nbsp;&nbsp;<br>&nbsp;&nbsp;&nbsp;&nbsp;For A = 1 To Len(NumberToCheck)<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;rst.AddNew<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;rst!Num = Mid(NumberToCheck, A, 1)<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;rst.Update<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Debug.Print<br>&nbsp;&nbsp;&nbsp;&nbsp;Next<br>&nbsp;&nbsp;&nbsp;&nbsp;rst.Close<br>&nbsp;&nbsp;&nbsp;&nbsp;Set rst = db.OpenRecordset(&quot;Select Num From Test Order By Num&quot<br>&nbsp;&nbsp;&nbsp;&nbsp;rst.MoveFirst<br>&nbsp;&nbsp;&nbsp;&nbsp;NewNum = &quot;&quot;<br>&nbsp;&nbsp;&nbsp;&nbsp;For A = 1 To Len(NumberToCheck)<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;NewNum = NewNum & Trim(rst!Num)<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;rst.MoveNext<br>&nbsp;&nbsp;&nbsp;&nbsp;Next<br>&nbsp;&nbsp;&nbsp;&nbsp;ReOrder1 = NewNum<br>&nbsp;&nbsp;&nbsp;&nbsp;rst.MoveLast<br>&nbsp;&nbsp;&nbsp;&nbsp;For A = Len(NumberToCheck) To 1 Step -1<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;rst.Delete<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;rst.MovePrevious<br>&nbsp;&nbsp;&nbsp;&nbsp;Next<br>End Function<br>------------------------<br>Create a table callled &quot;Test&quot; that has one field called &quot;Num&quot;<br><br> <p>DougP<br><a href=mailto: dposton@universal1.com> dposton@universal1.com</a><br><a href= > </a><br> Ask me how Bar-codes can help you be more productive.