I'm attempting to format a fax number on my report. If you have any ideas, let me know. Thanks. The following formulas return the following results:

=FAX_NUMBER = 8062562553

=Format$([FAX_NUMBER],"(000)000-0000") = #Error
=Format$(CLng([FAX_NUMBER]),"(000)000-0000") = 0
=Format$([FAX_NUMBER],"(@@@)@@@-@@@@") = #Error
=Format$(val([FAX_NUMBER]),"(000)000-0000") = #Error
=Format(val([FAX_NUMBER]),"(000)000-0000") = #Error
=Format([FAX_NUMBER],"(000)000-0000") = #Error
=Format([FAX_NUMBER],"""(###)###-####""") = #Error
=Format(CLng([FAX_NUMBER]),"""(###)###-####""") = (###)###-####

What is the datatype of the fax number in the table? Are any symbols stored with it? (hyphens or parenthesis)

Set this in the Input Mask property of the control to the desired output ...

RDH

The field is text. As I scroll through looking at the data, some of it has dashes in it and some has parentheses. When I began, I had a complex formula that checked for nulls and also had the REPLACE function for the extra characters. When that didn't work I removed the complexity and chose a record with no extra characters.

My suggestion would be to remove all special characters from the field. Then use an InputMask wherever your users are entering data so that no special characters are allowed.

Then you can use the Format function or InputMask function in your report and all will be fine. But your data HAS to be consistent for any kind of solution to work.