You can create a custom function to extract the number portion from a string. The code would go something like this:
Code:
Public Function getNumber(mystring As String) As Long
Dim holdnumeric As String
Dim i As Long
For i = 1 To Len(mystring)
If IsNumeric(Mid(mystring, i, 1)) Then
holdnumeric = holdnumeric & Mid(mystring, i, 1)
End If
Next i
getNumber = Val(holdnumeric)
End Function
You can then use the function in queries, reports, forms etc. I set the function up to return a long number. If the numeric portion falls outside of the field size requirements for a long number you may need to change it to a single or double precision number
From Access Help:Long Integer(Default) Stores numbers from –2,147,483,648 to 2,147,483,647 (no fractions).
I've attached a sample database that includes the function. I've also included a query that shows what the function returns.