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

[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

 

[krazymike]

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) ", "")
'********************************

 

[raskew via AccessMonster.com]

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

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:

[quoted text clipped - 17 lines]

Kelvin

 

[Kelvin]

thanks, that's what I was looking for!

Kelvin

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

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:

[quoted text clipped - 17 lines]

Kelvin

 

[Kelvin]

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

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

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:

[quoted text clipped - 17 lines]

Kelvin

 

[Marshall Barton]

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"

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,"(&&&) &&&-&&&&")))