Why is formatting not working in this statement?

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

Kelvin

Why if I have a phone number like 3607404567 will it not format the last
seven numbers?

This is my query statement.
test:
IIf(Left([PhoneDirectLine],3)="615",Right(Format([PhoneDirectLine],"&&&-&&&&"),7))

It returns the last seven numbers correctly but no formatting "7404567".

Any help would be appreciated

Kelvin
 
Kelvin said:
Why if I have a phone number like 3607404567 will it not format the
last seven numbers?

This is my query statement.
test:
IIf(Left([PhoneDirectLine],3)="615",Right(Format([PhoneDirectLine],"&&&-
&&&&"),7))

It returns the last seven numbers correctly but no formatting
"7404567".
Click to expand...
You only have two arguments for that IIF statement - it requires 3
arguments - it's not throwing an error?

IIf(
Left([PhoneDirectLine],3)="615",
Right(Format([PhoneDirectLine],"&&&-&&&&"),7)
)

Do I understand that you only want the formatting to occur if the string
starts iwth 615? What do you want to have happen when the string does
not start that way, as is the case with your example (360740567)?
 
Try the following:

IIf(Left([PhoneDirectLine],3)="615",Format(Right([PhoneDirectLine],7)"@@@-@@@@"))

Format the result of the Right function.

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================
 
This is a little different then my first question, but idealy I'd like this.

"6158445490" would return "5490" (starts with "615844")
"6155337643" would return "533-7643" (starts with "615")
"3608445490" would return "(360) 844-5490"

Thanks

Kelvin


Bob Barrows said:
Kelvin said:
Why if I have a phone number like 3607404567 will it not format the
last seven numbers?

This is my query statement.
test:
IIf(Left([PhoneDirectLine],3)="615",Right(Format([PhoneDirectLine],"&&&-
&&&&"),7))

It returns the last seven numbers correctly but no formatting
"7404567".
Click to expand...
You only have two arguments for that IIF statement - it requires 3
arguments - it's not throwing an error?

IIf(
Left([PhoneDirectLine],3)="615",
Right(Format([PhoneDirectLine],"&&&-&&&&"),7)
)

Do I understand that you only want the formatting to occur if the string
starts iwth 615? What do you want to have happen when the string does
not start that way, as is the case with your example (360740567)?
Click to expand...
 
Are those the only possibilities? I hope so ;-)

You will need a nested Iif

Iif(
Left([PhoneDirectLine],6) = "615844",
Right([PhoneDirectLine],4),
Iif(
Left([PhoneDirectLine],3)="615",
Format(Right([PhoneDirectLine],7),"@@@-@@@@"),
Format([PhoneDirectLine],"(@@@) @@@-@@@@")
)

This is a little different then my first question, but idealy I'd
like this.

"6158445490" would return "5490" (starts with "615844")
"6155337643" would return "533-7643" (starts with "615")
"3608445490" would return "(360) 844-5490"

Thanks

Kelvin


Bob Barrows said:
Kelvin said:
Why if I have a phone number like 3607404567 will it not format the
last seven numbers?

This is my query statement.
test:
Click to expand...
IIf(Left([PhoneDirectLine],3)="615",Right(Format([PhoneDirectLine],"&&&-
&&&&"),7))
It returns the last seven numbers correctly but no formatting
"7404567".
Click to expand...
You only have two arguments for that IIF statement - it requires 3
arguments - it's not throwing an error?

IIf(
Left([PhoneDirectLine],3)="615",
Right(Format([PhoneDirectLine],"&&&-&&&&"),7)
)

Do I understand that you only want the formatting to occur if the
string starts iwth 615? What do you want to have happen when the
string does not start that way, as is the case with your example
(360740567)?
Click to expand...
Click to expand...
 
Back
Top