Telephone number format in Query

[Kevin D.]

Hi All,

I have a query based on a table with a telephone number field that stores
the number thusly: 9993333333. I'd like the query to display the phone
number as such: [phone]. How can I do this?

Thanks for your help.

Kevin D.

[Jerry Whittle]

Put this in the field of a query with the proper field name between the [ ].

TheTelephone: Format([TheTelephoneNumberField],"(@@@) @@@-@@@@")
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

Post by Kevin D.
Hi All,
I have a query based on a table with a telephone number field that stores
the number thusly: 9993333333. I'd like the query to display the phone
number as such: [phone]. How can I do this?
Thanks for your help.
Kevin D.

[samiauthor]

Hello Jerry, I tried to do what you suggested here for the other post, but
still have a problem with it. This is what I have:
Business Phone: ([Business Phone],"(###) ###-####")

Can you please tell me what I'm missing?
Thank you, Sharon

Post by Jerry Whittle
Put this in the field of a query with the proper field name between the [ ].
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

Post by Kevin D.
Hi All,
I have a query based on a table with a telephone number field that stores
the number thusly: 9993333333. I'd like the query to display the phone
number as such: [phone]. How can I do this?
Thanks for your help.
Kevin D.

[John Spencer MVP]

You are missing the call to the format function.

Business Phone: Format([Business Phone],"(###) ###-####")

Although you probably want

Business Phone: Format([Business Phone],"(@@@) @@@-@@@@")

And that can still cause errors, so you might need

Business Phone: Format([YOUR TABLE NAME].[Business Phone],"(@@@) @@@-@@@@")

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

Post by samiauthor
Hello Jerry, I tried to do what you suggested here for the other post, but
Business Phone: ([Business Phone],"(###) ###-####")
Can you please tell me what I'm missing?
Thank you, Sharon

Post by Jerry Whittle
Put this in the field of a query with the proper field name between the [ ].
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

Post by Kevin D.
Hi All,
I have a query based on a table with a telephone number field that stores
the number thusly: 9993333333. I'd like the query to display the phone
number as such: [phone]. How can I do this?
Thanks for your help.
Kevin D.

[samiauthor]

That did the trick, thank you.

Post by samiauthor
Hello Jerry, I tried to do what you suggested here for the other post, but
Business Phone: ([Business Phone],"(###) ###-####")
Can you please tell me what I'm missing?
Thank you, Sharon

Post by Jerry Whittle
Put this in the field of a query with the proper field name between the [ ].
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

Post by Kevin D.
Hi All,
I have a query based on a table with a telephone number field that stores
the number thusly: 9993333333. I'd like the query to display the phone
number as such: [phone]. How can I do this?
Thanks for your help.
Kevin D.

[m***@gmail.com]

Thanks for posting guys, I was able to update my records as well. :)

[Marshall Barton]

Post by Kevin D.
I have a query based on a table with a telephone number field that stores
the number thusly: 9993333333. I'd like the query to display the phone
number as such: [phone]. How can I do this?

Assuming the field is a Text field, set the format property
of whatever is used to display (a form/report text box) the
phone number to:

(@@@) @@@-@@@@