Remove ( and ) and -

Sam

Could someone help me by providing a formula that will change the
formatting, in a query of a phone number.. The data in the table is
formatted as (123) 456-7890. I need the field to be 123456789 without
changing the actual table.

Any help would be appreciated

Sep 24 '08 #1

fredg

On Wed, 24 Sep 2008 10:21:47 -0700 (PDT), Sam wrote:

Could someone help me by providing a formula that will change the
formatting, in a query of a phone number.. The data in the table is
formatted as (123) 456-7890. I need the field to be 123456789 without
changing the actual table.

Any help would be appreciated

exp: Replace(Replace(Replace(Replace([Phone],"(",""),")",""),"-",""),"
","")

Sep 24 '08 #2

fredg

On Wed, 24 Sep 2008 11:38:10 -0700, fredg wrote:

On Wed, 24 Sep 2008 10:21:47 -0700 (PDT), Sam wrote:

>Could someone help me by providing a formula that will change the
formatting, in a query of a phone number.. The data in the table is
formatted as (123) 456-7890. I need the field to be 123456789 without
changing the actual table.

Any help would be appreciated

exp: Replace(Replace(Replace(Replace([Phone],"(",""),")",""),"-",""),"
","")

Sorry, I didn't mean to not sign the reply.
--
Fred
Please respond only to this newsgroup.
I do not reply to personal e-mail

Sep 24 '08 #3

Sam

On Sep 24, 2:13*pm, fredg <fgutk...@example.invalidwrote:

On Wed, 24 Sep 2008 11:38:10 -0700, fredg wrote:

On Wed, 24 Sep 2008 10:21:47 -0700 (PDT), Sam wrote:

Could someone help me by providing a formula that will change the
formatting, in a query of a phone number.. The data in the table is
formatted as (123) 456-7890. *I need the field to be 123456789 without
changing the actual table.

Any help would be appreciated

exp: Replace(Replace(Replace(Replace([Phone],"(",""),")",""),"-",""),"
","")

Sorry, I didn't mean to not sign the reply.
--
Fred
Please respond only to this newsgroup.
I do not reply to personal e-mail

Thanks Fred, this works except now I have the issue that if the phone
number field is blank I am getting a #Error I tried adding an iif
statement but I can not get the blanks to be blanks.

Any suggestions?
Sam

Sep 25 '08 #4

fredg

On Thu, 25 Sep 2008 05:39:04 -0700 (PDT), Sam wrote:

On Sep 24, 2:13*pm, fredg <fgutk...@example.invalidwrote:

>On Wed, 24 Sep 2008 11:38:10 -0700, fredg wrote:

>>On Wed, 24 Sep 2008 10:21:47 -0700 (PDT), Sam wrote:

>>>Could someone help me by providing a formula that will change the
formatting, in a query of a phone number.. The data in the table is
formatted as (123) 456-7890. *I need the field to be 123456789 without
changing the actual table.

>>>Any help would be appreciated

>>exp: Replace(Replace(Replace(Replace([Phone],"(",""),")",""),"-",""),"
","")

Sorry, I didn't mean to not sign the reply.
--
Fred
Please respond only to this newsgroup.
I do not reply to personal e-mail

Thanks Fred, this works except now I have the issue that if the phone
number field is blank I am getting a #Error I tried adding an iif
statement but I can not get the blanks to be blanks.

Any suggestions?
Sam

In your query, as criteria on the [Phone] field column, write:

Is Not Null

--
Fred
Please respond only to this newsgroup.
I do not reply to personal e-mail

Sep 25 '08 #5