Sorting Numbers within A String

[TituscroW]

Hello,
This is my first post, so I hope I include all the right information.

I have a Table which has a list of numbers stored in a string, which are not
in numerical order
For example "10 2 35 21 42 3 6" etc

My aim is to create a function which will put these in to numerical order
like "2 3 6 10 21 35 42"

If anyone could point me in the right direction it would be greatly
appreciated.

Thank you in advance
TituscroW

[mal_lori]

If there are ONLY numbers in this field....then why is it a string??
that would make it a whole lot simpler...

The other Option is to bring the table into a query..
and make a calculated field with the VAL function.
something like... NumericalValue: Val(theFieldName)
and then sort this on this column.

IF you mean that each record has a string with a list of numbers "2 3 6
10" etc. and you want to pull it apart, order it, and put it back
together....then that would take a little more work - probably
something like...

* break up the string into a variable array using the Left, Mid, Right
functions (or the ExtractStr function below)
* sort the array
* rebuild the string.

Hope this helps,
Mal.

Beware of work wrap in this copied/pasted example.
==================================================
Function ExtractStr(strIn, intNeedSegment, strDelimiter, Optional
strNotFound As String) As String

' Function to chop a input string into segments and return the
requested segment
' Written and developed by Thomas M. Brittell
' Copyright 1998; All rights reserved.
'
' strIn - Input string to be segmented
' intNeedSegment - Indicates the segment to be returned
' strDelimiter - The delimiter used to seperate each segment
' strNotFound - When no segment is found return the specified
string if provided
'
'** USAGE: extractStr(mystr,5,",","Not Found")
' from "hello,this,is,my,joined,string'
' would return "joined"

Dim intCurrentPosition As Integer
Dim intFoundPosition As Integer
Dim intLastPosition As Integer
Dim intGetSegment As Integer
Dim wrkNotFound As String

If IsEmpty(strNotFound) Or strNotFound = "" Then
wrkNotFound = ""
Else
wrkNotFound = strNotFound
End If

If strIn = "" Or IsNull(strIn) = True Then strIn = ""

intCurrentPosition = 0
intFoundPosition = 0
intLastPosition = 0
intGetSegment = intNeedSegment

Do While intGetSegment > 0
intLastPosition = intCurrentPosition
'Find a occurance of the delimiter
intFoundPosition = InStr(intCurrentPosition + 1, strIn,
Left$(strDelimiter, 1))
If intFoundPosition > 0 Then
intCurrentPosition = intFoundPosition
intGetSegment = intGetSegment - 1
Else
'End of input string so exit
intCurrentPosition = Len(strIn) + 1
Exit Do
End If
Loop
'If nothing was found and you had at least one delimiter return ""
If (intFoundPosition = 0) And ((intGetSegment <> intNeedSegment)
And (intGetSegment > 1)) Then
ExtractStr = wrkNotFound
Else
'Return the segment between the last position and the current
one
ExtractStr = Mid$(strIn, intLastPosition + 1,
intCurrentPosition - intLastPosition - 1)
End If

End Function

[TituscroW]

Thanks Mal,

I will apply this function and let you know how I go.

The string is all in numbers so I can put each number into individual fields.
However, I think I have the same issue.
I can compare any one field to another and move it left or right accordingly.
But I have trouble doing this for several fields (Numbers) over 1,000's of
records.

Thanks again
TituscroW

[Larry Linson]

You should be able to use the Split function to separate the text numbers
into an array. There are various sort algorithms documented (Google this and
various VB newsgroups for references) to sort arrays -- get one and use it.
Then, if you feel compelled to continue the non-relational "multi-value"
field, format each and concatenate it back into a string. If you don't feel
so compelled, just write them to a table, with a foreign key pointing back
to the original record. Then you will sort them in the Query you use to
retrieve them from that table.

Larry Linson
Microsoft Access MVP

"TituscroW" <u15606@uwe> wrote in message news:5720333786182@uwe...

Thanks Mal,

I will apply this function and let you know how I go.

The string is all in numbers so I can put each number into individual
fields.
However, I think I have the same issue.
I can compare any one field to another and move it left or right
accordingly.
But I have trouble doing this for several fields (Numbers) over 1,000's of
records.

Thanks again
TituscroW