How do I format a phone number with out or without the area code?

  • Thread starter Thread starter Kelvin
  • Start date Start date
K

Kelvin

I'm using this string in my query but it's not doing everything I want done:
CellPhone:
IIf(Left([CellPhoneNumber],3)="615",Right(Format([CellPhoneNumber],"&&&-&&&&"),7))

The data in [CellPhoneNumber] looks like this "6155942648".
If the area code is "615" I want it to only return the phone number
"5942648" but I also want the number formatted to look like "594-2648", with
the dash.
If the area code is something other then "615" I want the formatting to be
"(&&&) &&&-&&&&". I haven't worked on this part yet.

"6155942648" should look like "594-2648"
"3605942648" should look like "(360) 594-2648"

I can't seem to figure out how to get the data returned to be formatted...

Can someone point me in the right direction to get this formatting to work?

Thanks

Kelvin
 
Something like this?

'*********************************
Select Case Len(strPhone)
Case Is 10
strFormatted = Format$(strPhone, "(###) ###-####")
Case Is 7
strFormatted = Format$(strPhone, "###-####")
Case Else
MsgBox "Error in Phone Number!"
End Select

If InStr(strFormatted, "(615) ") > 0 Then strFormatted = Replace$
(strFormatted, "(615) ", "")
'********************************
 
thanks, that's what I was looking for!

Kelvin

raskew via AccessMonster.com said:
Or, something like this:

x = "6155942648"
? (iif(left(x,3) = "615", format(x, "(###)###-####"), format(mid(x,4),
"###-
####")))
(615)594-2648

x = "5155942648"
? (iif(left(x,3) = "615", format(x, "(###)###-####"), format(mid(x,4),
"###-
####")))
594-2648

Bob said:
Something like this?

'*********************************
Select Case Len(strPhone)
Case Is 10
strFormatted = Format$(strPhone, "(###) ###-####")
Case Is 7
strFormatted = Format$(strPhone, "###-####")
Case Else
MsgBox "Error in Phone Number!"
End Select

If InStr(strFormatted, "(615) ") > 0 Then strFormatted = Replace$
(strFormatted, "(615) ", "")
'********************************
I'm using this string in my query but it's not doing everything I want
done:
CellPhone:
Click to expand...
[quoted text clipped - 17 lines]
Kelvin
Click to expand...
Click to expand...
Click to expand...
 
How about if I want everything that starts with "615844" to return the last
four digits?
So "6158445490" would return "5490" (starts with "615844")
and "6155337643" would return "533-7643" (starts with "615")
and "3608445490" would return "(360) 844-5490"

Your help is much appreciated!!!

Kelvin

raskew via AccessMonster.com said:
Or, something like this:

x = "6155942648"
? (iif(left(x,3) = "615", format(x, "(###)###-####"), format(mid(x,4),
"###-
####")))
(615)594-2648

x = "5155942648"
? (iif(left(x,3) = "615", format(x, "(###)###-####"), format(mid(x,4),
"###-
####")))
594-2648

Bob said:
Something like this?

'*********************************
Select Case Len(strPhone)
Case Is 10
strFormatted = Format$(strPhone, "(###) ###-####")
Case Is 7
strFormatted = Format$(strPhone, "###-####")
Case Else
MsgBox "Error in Phone Number!"
End Select

If InStr(strFormatted, "(615) ") > 0 Then strFormatted = Replace$
(strFormatted, "(615) ", "")
'********************************
I'm using this string in my query but it's not doing everything I want
done:
CellPhone:
Click to expand...
[quoted text clipped - 17 lines]
Kelvin
Click to expand...
Click to expand...
Click to expand...
 
Kelvin said:
How about if I want everything that starts with "615844" to return the last
four digits?
So "6158445490" would return "5490" (starts with "615844")
and "6155337643" would return "533-7643" (starts with "615")
and "3608445490" would return "(360) 844-5490"
Click to expand...


A small change to my original reply:

IIf(Left(CellPhoneNumber,6)="615844",
Format(Right(CellPhoneNumber,4),"&&&&"),
IIf(Left(CellPhoneNumber,3)="615",
Format(Right(CellPhoneNumber,7),"&&&-&&&&"),
Format(CellPhoneNumber,"(&&&) &&&-&&&&")))
 
Back
Top