Format Phone in Query Expression

[Tom]

I use the Input Format "!\(999") "000\-0000;;_" for a phone number field.

In a query now, I want to concatenate the phone number and the extension
(please see next line).

Expr1: IIf([Extension] Is Not Null,[Work] & " - Ext. " & [Extension],[Work])


However, the Input Format of the phone number is now being ignored. So,
right now it phone numbers
are shown as "8001234567 - Ext. 123"

Does anyone know how to put the specified phone number format into the
expression so the it will look like this

(800) 123-4567 - Ext. 123
or
(800) 9876-5432

 

[fredg]

I use the Input Format "!\(999") "000\-0000;;_" for a phone number field.

In a query now, I want to concatenate the phone number and the extension
(please see next line).

Expr1: IIf([Extension] Is Not Null,[Work] & " - Ext. " & [Extension],[Work])

However, the Input Format of the phone number is now being ignored. So,
right now it phone numbers
are shown as "8001234567 - Ext. 123"

Does anyone know how to put the specified phone number format into the
expression so the it will look like this

(800) 123-4567 - Ext. 123
or
(800) 9876-5432

Tom,
You're confusing an Input Mask with the Format property.
They are two different things.
As written, your Input Mask assists in data entry, but is not saved
with the data.
To actually save the mask with the data, change the ending
from ;;_ to ;0;_
but that won't help with previously entered data.

The Format property determines how the data in the field is actually
going to be displayed.

To temporarily resolve this problem in this instance, use the
following expression:

Expr1: IIf([Extension] Is Not Null,Format([Work],"(@@@) @@@-@@@@") & "
- Ext. " & [Extension],Format([Work],"(@@@) @@@-@@@@"))

You can run an Update query to change the current Phone data to the
new format. Then the data will reflect the wanted format and it will
not be necessary to write it into the query.

Update YourTable Set YourTable.[Phonefield] =
Format([PhoneField],"(@@@) @@@-@@@@")
Where YourTable.Phonefield Is Not Null;

 

[Tom]

Fred:

Thanks for providing me the feedback and the update query. I now understand
the difference between the Input Mask and Format.

I made the changes accordingly and it works great.

Thanks again,
Tom

I use the Input Format "!\(999") "000\-0000;;_" for a phone number field.

In a query now, I want to concatenate the phone number and the extension
(please see next line).

Expr1: IIf([Extension] Is Not Null,[Work] & " - Ext. " & [Extension],[Work])

However, the Input Format of the phone number is now being ignored. So,
right now it phone numbers
are shown as "8001234567 - Ext. 123"

Does anyone know how to put the specified phone number format into the
expression so the it will look like this

(800) 123-4567 - Ext. 123
or
(800) 9876-5432

Tom,
You're confusing an Input Mask with the Format property.
They are two different things.
As written, your Input Mask assists in data entry, but is not saved
with the data.
To actually save the mask with the data, change the ending
from ;;_ to ;0;_
but that won't help with previously entered data.

The Format property determines how the data in the field is actually
going to be displayed.

To temporarily resolve this problem in this instance, use the
following expression:

Expr1: IIf([Extension] Is Not Null,Format([Work],"(@@@) @@@-@@@@") & "
- Ext. " & [Extension],Format([Work],"(@@@) @@@-@@@@"))

You can run an Update query to change the current Phone data to the
new format. Then the data will reflect the wanted format and it will
not be necessary to write it into the query.

Update YourTable Set YourTable.[Phonefield] =
Format([PhoneField],"(@@@) @@@-@@@@")
Where YourTable.Phonefield Is Not Null;