Solved Extracting ONLY Numbers from a Field to be used for Sorting (1 Viewer)

[Bean Machine]

Hi All!

I am just wondering if there is a method of only pulling numbers from a field in an Access query. Basically, I want to be able to use a field with just the numbers as a means of sorting and filtering later on in this database but I can't use Left() or Right() (to my knowledge anyway) as the numbers go above single digits. I have attached an image with the field I am trying to extract the numbers from. Any help would be greatly appreciated.

 

[MajP]

Order By VAL([fld_PLI])

Val function (Visual Basic for Applications)

Office VBA reference topic

learn.microsoft.com

 

[Bean Machine]

Order By VAL([fld_PLI])

Val function (Visual Basic for Applications)

Office VBA reference topic

learn.microsoft.com

Awesome, thank you very much! I figured there would be a function for it. Have a great day!

 

[sxschech]

Adding this option for cases where there is text before the numbers, would need an alternative function as val would return 0.

Example:
? val("b12b")
0

Function GetNumber(ByVal pStr As String) As Long
'https://www.access-programmers.co.uk/forums/showthread.php?t=219652
'20180711
    Dim intLen  As Long
    Dim N       As Long
    pStr = Trim(pStr) 'removes leading & trending spaces
    intLen = Len(pStr) 'stores original length
    N = 1 'consider this a counter & position marker
    If pStr = "" Or IsNull(pStr) Or intLen = 0 Then Exit Function 'validate we didn't get passed an empty/null string
    Do
        If IsNumeric(Mid(pStr, N, 1)) Then 'check if that single character is a number
            GetNumber = GetNumber & Mid(pStr, N, 1) 'if it is add to existing ones if any
            N = N + 1 'add to counter so we know to go to next character on the next pass/loop
        Else
            N = N + 1 'it wasn't a number, add to counter so we know to skip it
        End If
    Loop Until intLen = (N - 1) 'go until we processed all characters. The reason we have to do n-1 is that Len starts at 0 & we told n to start at 1
End Function 'if no numbers function will return default value of data type, in our case long would be 0


? getnumber("b12b")
12