format phone number

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

hi, im trying to remove the non numerical chars from a phone num. im using
this:
fax = Format((rstlist!PhoneNum), "##########")
and that takes out the dashes but not the () . how can i remove the
parenthesis also?
thanks for any help
 
Hey peanut,

First, rather then use VBA, I would use an input mask on the field and only
store the number in the table. But you can use Mid to capture only the
numbers.

Phone/Fax numbers are 10 digits long, so:

Mid (fax, 2, 10)

In your example,

fax = Mid(Format((rstlist!PhoneNum), "##########"), 2, 10)
 
I tried your format, and it did not work for me. One way would be to use a
few Replace functions to strip out unwanted characters:
fax = rstlist!PhoneNum
fax = Replace(fax,"(","")
fax = Replace(fax,")","")
fax = Replace(fax,"-")
fax = Replace(fax," ","")

Or another way would be:
fax = FixFax(rstlist!PhoneNum)

Function FixFax(strPhoneNo as string) As String
dim lngX as Long
dim strRetVal
For lngX = 1 to Len(strPhoneNo)
If IsNumeric(Mid(strPhoneNo,lngX,1)) Then
strRetVal = (Mid(strPhoneNo,lngX,1))
End If
Next lngX
FixFax = strRetval
End Function
 
hi, yes thats helpful, thanks. actually, i do have an input mask and it is
set to store the number only, not the formatting, so i dont understand why i
still see the formatting when in the table ... im thinking maybe the input
mask should be on the form only, not the table itself?
 
Back
Top