This will require a custom vba function to strip out the non-numeric
characters.
Paste the two functions below into a VBA module and call one of them in your
query. They work slightly different.
Field: fStripToNumbersOnly(YourFieldName)
Public Function fStripToNumbersOnly(ByVal varText As Variant) As String
'Takes input and returns only the numbers in the input. Strips out
'all other characters. Handles nulls, dates, numbers, and strings.
Const strNumbers As String = "0123456789"
Dim strOut As String
Dim intCount As Integer
If Len(varText & "") = 0 Then
strOut = ""
Else
varText = varText & ""
For intCount = 1 To Len(varText)
If InStr(1, strNumbers, Mid(varText, intCount, 1)) > 0 Then
strOut = strOut & Mid(varText, intCount, 1)
End If
Next intCount
End If
fStripToNumbersOnly = strOut
End Function
Public Function NumPart(strIn As String) As String
'Faster than fStripToNumbersOnly but it doesn't handle nulls
'or other data types
Dim iPos As Integer
Dim strCh As String
NumPart = ""
For iPos = 1 To Len(strIn)
strCh = Mid(strIn, iPos, 1)
If IsNumeric(strCh) Then
NumPart = NumPart & strCh
End If
Next iPos
End Function
--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..
