Input Mask in report for phone numbers

[PhoneTech2]

I have a customer list that includes phone numbers. I am not allowed to change the customer table. The phone numbers are entered one number after another ( 18005551212 ).

The numbers are a mixture of seven digits ( 555-1212) and ten digits ( 1-800-555-1212 )

When I do a report, I would like to add some separators between the numbers ( 1 (800) 555-1212 ).

I have tried to put these instruction in the INPUT MASK of the number field ( !\(999&quot"000-0000;0;# ).

The seven digit numbers come out like this: ( )555-1212.
The ten digit numbers come out like this 18005551212.

What am I doing wrong?

Thank you for your help.

 

[grnzbra]

Try playing with something like this. I'm not sure where you would put it. Perhaps in the OnCurrent event for the report.
If Len(phonenumber) = 10
Me.PhoneNumberTextbox.Format = !\(999&quot"000-0000;0;#
Else
Me.PhoneNumberTextbox.Format = !\000-0000;0;#
End if

Or, perhaps, in the format property of the textbox:

IIf(Len(PhoneNumber) = 10, !\(999&quot"000-0000;0;# , !\000-0000;0;# )

 

[DKH]

Assume "X" = table field name containing number

try this code in the query column for the number

IIF(["X"]>9999999,(left(["X"],1))&"-("&(mid(["X"],2,3))&&quot"&(mid(["X"],5,3))&"-"&(mid(["X"],8,4))&&quot","("&(left(["X"],3))&"-"&(right(["X"],4)))

 

[PhoneTech2]

Thank you Grnbra and DKH.

I ended up using DKH (mainly because I could cut and paste). Turned out there was one extra &quot" and it ended up at the end of the number (I think it was after the 8,4))&&quot&quot. Anyway, I was able to remove it, and it now does exactly what I wanted it to do.

Thank you both again for your help.