Stripping out Phone number characters 1

[saavik]

What is the proper expression in an Update query that would strip out all the non numeric characters in a phone number such as the parentheses and hyphens. A phone number example would be (717)865-0606. Would like to strip out everything except the string of numbers.
Thank you

 

[rhicks]

Use the Val function. This function will return only the numeric character in a string.

If the string is "189 North 25th Avenue"

The Val function would return: 18925

See Access help for more info on this function.

HTH X-)
RDH
Ricky Hicks
rdhicks@mindspring.com

 

[PaulF]

When I attempted to use the Val function on the phone #, it returned 0

MsgBox Val("(717)865-0606&quot, , "Value of Phone # "

however using a Parsing function I have, it returned
7178650606. The function will also return Alphas.
It was designed to strip out spaces and special characters, in order to use it for numerics only, you'd comment out
Case 48 to 57 and Case 65 to 90 info.

MsgBox ParseIt("(717)865-0606&quot, , "Parsed Value"

Public Function ParseIt(strToBeParsed As String)
' This function will parse any character except alpha characters
' and numeric characters from a string, including any spaces
Dim lngLength As Long, i As Long, ParsedString As String
i = 1
lngLength = Len(strToBeParsed)
For i = 1 To lngLength
Select Case Asc(Mid(strToBeParsed, i, 1))
Case 48 To 57
ParsedString = IIf(IsNull(ParsedString), Mid(strToBeParsed, i, 1), ParsedString & Mid(strToBeParsed, i, 1))
Case 65 To 90
ParsedString = IIf(IsNull(ParsedString), Mid(strToBeParsed, i, 1), ParsedString & Mid(strToBeParsed, i, 1))
Case 97 To 122
ParsedString = IIf(IsNull(ParsedString), Mid(strToBeParsed, i, 1), ParsedString & Mid(strToBeParsed, i, 1))
Case Else
ParsedString = IIf(IsNull(ParsedString), "", ParsedString)
End Select
Next i
ParseIt = ParsedString
End Function

PaulF