Phone Number Formatting

[kermit5]

Is there a way to define in a report a format that would change a table value of 8011234567 to (801)123-4567?
.
I am using the following code:
.
="Phone: " & Iif ([Phone] = 0,[ContractorPhone],[Phone]& " Fax: " & (Iif ([Fax] = 0, [ContractorFax], [Fax])

The output looks like this:
Phone: 8011234567 Fax: 8017654321

But I want it to look like:
Phone: (801)123-4567 Fax: (801)765-4321

Any Ideas?

 

[pcs]

assuming that the phone/fax numbers are ALWAYS exactly 10 characters...
create 2 unbound text boxes on your report with the control source set as:

="("+Left([YourPhoneField],3)+")"+Mid([YourPhoneField],4,3)+"-"+Right([YourPhoneField],4)
="("+Left([YourFaxField],3)+")"+Mid([YourFaxField],4,3)+"-"+Right([YourFaxField],4)

change the label on each to read: Phone and Fax...

hth,
al