Remove text and keep only numbers

[Guest]

Does anyone know how I can tell Access to extract and display all NUMBERS
from an alpha-numeric field? I.E. I have a field:

Tab 10mg.dl
Dosage 20mg
50cg strength

All I want is a new column that shows the numbers:
10
20
50

so I can calculate them on a report. Thanks everyone!

 

[Guest]

You can use the function below to return only the numeric parts of a string:

Public Function JustTheNumbers( strAllOfIt As String) as String
Dim lngLoop As Long
Dim strReturn As String

For lngLoop = 1 To Len(strAllOfIt)
If IsNumeric(Mid(strAllOfIt,lngLoop,1)) Then
strReturn = strReturn & Mid(strAllOfIt,lngLoop,1)
End If
Next lngLoop

JustTheNumbers = strReturn

End Function

 

[Guest]

Thanks so much. And just where would I place this code in the query?

 

[Guest]

You don't put the code in the query, you put the code in a standard module.
You call the function in your query using a calculated field:

NumbersOnly: JustTheNumbers([SomeField])

Where [SomeField] is the field you want converted

 

[Guest]

WOW. That's perfect! Thank you.

You don't put the code in the query, you put the code in a standard module.
You call the function in your query using a calculated field:

NumbersOnly: JustTheNumbers([SomeField])

Where [SomeField] is the field you want converted

--
Dave Hargis, Microsoft Access MVP

Thanks so much. And just where would I place this code in the query?