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: (999) 333-3333. 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],"(@@@) @@@-@@@@")

 

[Marshall Barton]

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: (999) 333-3333. 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:

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

 

[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
(e-mail address removed)

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.

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: (999) 333-3333. 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

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
(e-mail address removed)

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.

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: (999) 333-3333. How can I do this?

Thanks for your help.

Kevin D.

 

[samiauthor]

That did the trick, thank you.

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
(e-mail address removed)

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.

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: (999) 333-3333. How can I do this?

Thanks for your help.

Kevin D.

 

[mauro.2068]

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