Why is formatting not working in this statement?

[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

 

[Bob Barrows]

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".

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)?

 

[John Spencer]

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
'====================================================

 

[Kelvin]

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

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".

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)?

 

[Bob Barrows]

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

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".

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)?