Format Telephone number with text in report control

[chuckcoleman]

I'm having trouble with something that should be simple, (OK it will be simple after I get some help). I have a text box on a Access report that I want to contain some text plus a formated telephone number. For instance, in addition to a company's address I want a text box that displays: "Office Telephone (303) 555-1212". I've tried:

= "Office Telephone "& Format([OfcTel],"(###) ###-####"). As soon as this is input into the Control Source for the field Access changes it to

= "Office Telephone "& Format([OfcTel],"(#) #-#")

I've tried

= "Office Telephone "& Format ([OfcTel],"\(###") "###\-####") but Access says The expression you entered contains invalid syntax.

Any suggestions?

 

[chuckcoleman]

SOLVED: Used "Office Telephone "& Format([OfcTel],"(000) 000-000").

I guess it works because when using the Format function, # is an optional digit or space whereas 0 is a digit that is required. Therefore, my interpetation is that Access removes each # sign in each of the three parts to a telephone number and leaves only one but since the 0 is required, it doesn't remove them.

 

[boblarson]

Your assumption is correct. # is optional whereas 0 says to display it as a 0 if it is missing from that spot.