Results 1 to 6 of 6

20030114, 13:37 #1
 Join Date
 May 2001
 Location
 Maryland, USA
 Posts
 105
 Thanks
 0
 Thanked 0 Times in 0 Posts
sorting numbers in a text field (2000)
I am trying to sort by numerical value in a text field (I want 11 to come before 100.) I sure I remember that there is a function for this but can't remember what it is and can't find it in "help."
Thanks,
Eric

20030114, 13:49 #2
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 30 Times in 30 Posts
Re: sorting numbers in a text field (2000)
You can't sort by numerical value directly, but in a query, you can add a calculated column with expression
NumericValue: Val([TextField])
where TextField is the name of your text field containing numbers, and sort on that column. Instead of Val, you can also use CLng or CDbl, depending on the kind of numbers you have (CDbl if they contain decimals, CLng if they don't).

20030114, 14:09 #3
 Join Date
 May 2001
 Location
 Maryland, USA
 Posts
 105
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: sorting numbers in a text field (2000)
Thanks, I did remember that Val was the function, but when I tried it, it removes all nonnumeric characters. I've gotten around it by using two columns in the query. The first is the Val function, the second is the field I want sorted. Then I sort by both fields. The only thing that doesn't come out right is that some of the entries have #1, #2, etc. at the end. At this point it goes back to sorting #30 before #4.
Thanks again,
Eric
I tried the CDbl function, but that seems to be giving me the #NAME for any entry that has nonnumeric characters.

20030114, 14:26 #4
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 30 Times in 30 Posts
Re: sorting numbers in a text field (2000)
Aha, it wasn't clear from your original question that the field contained a mixture of text and numbers. If the values have different formats, it can be difficult to extract the numeric value correctly. For values of the form "Chanel #5", you can use
Val(Mid([FieldName],InStr([FieldName],"#")+1))
where FieldName is the name of the text field, but this won't help for values of the form "Chanel No 5". If you really have a lot of different formats, you should either consider storing the data in another way (if possible), or write a VBA function that parses the value and extracts the numeric value; you can use userdefined functions in Access queries. You have to know VBA to do that, though.

20030114, 23:35 #5
 Join Date
 Nov 2001
 Location
 Arlington, Virginia, USA
 Posts
 1,394
 Thanks
 0
 Thanked 3 Times in 3 Posts
Re: sorting numbers in a text field (2000)
<P ID="edit" class=small>(Edited by MarkD on 14Jan03 19:35. Added additional comment.)</P>Here is example of simple userdefined function used to return numerical value from text field that may have numbers, letters & symbols combined (uses VB Val function). This assumes only 1 number contained in a given field, and that these are whole numbers (integer values). Example:
Public Function GetNumVal(ByVal strText As String) As Long
Dim intLen As Integer
Dim n As Integer
Dim strNumVal As String
intLen = Len(strText)
For n = 1 To intLen
If IsNumeric(Mid(strText, n, 1)) Then
strNumVal = strNumVal & Mid(strText, n, 1)
End If
Next n
GetNumVal = Val(strNumVal)
End Function
Example of use in query to sort text field by numerical value:
SELECT GetNumVal([Text2]) AS [Num Val], Table1.Text2
FROM Table1
ORDER BY GetNumVal([Text2]), Table1.Text2;
In example Text2 is text field in Table1 that may contain numerical values. Note if text contains no numbers, function returns zero (0).
PS: If some of text fields have numbers and some do not, and you want to sort table so the numerical values are listed first, then use SQL like this:
SELECT GetNumVal([Text2]) AS [Num Val], Table1.Text2
FROM Table1
ORDER BY GetNumVal([Text2])=0 DESC , GetNumVal([Text2]), Table1.Text2
HTH

20030115, 09:29 #6
 Join Date
 Nov 2001
 Location
 Arlington, Virginia, USA
 Posts
 1,394
 Thanks
 0
 Thanked 3 Times in 3 Posts
Re: sorting numbers in a text field (2000)
Additional comments: If text string has more than one group of numerical values separated by nonnumeric characters, this function can be used to return an array of the numerical values:
Public Function GetNumValArray(ByVal strText As String) As Long()
' Returns an array of all number values found in string
' If no number found returns array with one element = 0
Dim n As Long ' For Loop counter
Dim i As Integer ' Array index counter
Dim strNumVal As String ' String of numerical characters
Dim lngNumVal() As Long ' Dynamic array
' Pad text w/space in case last char in string is numeric:
strText = strText & Chr(32)
i = 0
ReDim lngNumVal(0) ' Initialize array
For n = 1 To Len(strText)
If IsNumeric(Mid(strText, n, 1)) Then
strNumVal = strNumVal & Mid(strText, n, 1)
End If
If Not IsNumeric(Mid(strText, n, 1)) And Val(strNumVal) > 0 Then
If i > 0 Then
ReDim Preserve lngNumVal(i)
End If
lngNumVal(i) = Val(strNumVal)
strNumVal = ""
i = i + 1
End If
Next n
GetNumValArray = lngNumVal
' Test purposes only  print array values:
' For n = 0 To UBound(lngNumVal)
' Debug.Print "Array(" & n & "): " & lngNumVal(n)
' Next n
Erase lngNumVal
End Function
Then to get first or last numerical value found in string, use this function:
Public Function GetNumValRev(ByVal strText As String, _
ByVal intNum As Integer) As Long
' strText = text string to be evaluated
' intNum: 1= 1st number found, 2 = last number found in string
Dim lngTemp() As Long
lngTemp = GetNumValArray(strText)
Select Case intNum
Case 1 ' 1st number in array
GetNumValRev = lngTemp(LBound(lngTemp))
Case 2 ' last number in array
GetNumValRev = lngTemp(UBound(lngTemp))
End Select
Erase lngTemp
End Function
The second function could be modified if necessary to obtain number other than first or last, but it would be more convoluted. Second function can be used in query for sorting purposes. If the text string being evaluated never has more than one number, then you should use the simpler function shown in previous reply, as it would be a lot more efficient.
HTH