Results 1 to 6 of 6
  1. #1
    2 Star Lounger
    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

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 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).

  3. #3
    2 Star Lounger
    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 non-numeric 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 non-numeric characters.

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 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 user-defined functions in Access queries. You have to know VBA to do that, though.

  5. #5
    Bronze Lounger
    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 14-Jan-03 19:35. Added additional comment.)</P>Here is example of simple user-defined 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

  6. #6
    Bronze Lounger
    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 non-numeric 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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •