Format phone numbers in concatenation?

[allen_jr]

Hello,

My table data contains phone numbers as raw digits, ie: 9101234567 instead of (910)123-4567.. i used an input mask so that what I see is the "(910)123-4567"

My problem is, when I run a report, and create a concatenation formula such as:
=[FirstName] & " " & [LastName] & " -- " & [Cell]

What I end up with is:
John Smith -- 9101234567

But what I want is:
John Smith -- (910)123-4567

Can someone explain to me how to do this... I am fairly new to access am navigating through this with some books I picked up... I'm pretty sure I can add some type of formula condition or something, but not sure what to do...

Thanks for the help!

 

[boblarson]

How about:

=[FirstName] & " " & [LastName] & " -- " & Format([Cell], "(000)000-0000")

 

[allen_jr]

AWESOME!

Thanks so much, worked perfectly... I knew there was some little snippet of code that I needed... thanks!!

 

[allen_jr]

1 more question...

Is there a way to just join 2 text boxes, so that I can format the phone number in grey and the name in black? I want the info to flow together, like concatenation allows, but I want the info to display differently...

Is that possible?

Thanks!