Change Phone# Format

[JamesJ]

Hi. I imported over 200 contacts into my Contacts table. The imported
data's phone number format is: (###) ###-####. I want to change it to:
###-###-####. Can someone please show me an update query to
change this format so I don't need to do each individual record?

Thanks,
James

 

[John Vinson]

Hi. I imported over 200 contacts into my Contacts table. The imported
data's phone number format is: (###) ###-####. I want to change it to:
###-###-####. Can someone please show me an update query to
change this format so I don't need to do each individual record?

Thanks,
James

You can use the Mid() function to extract the pieces. Try updating to
(test this first and back up your database!!!):

Mid([Phone], 2, 3) & "-" & Right([Phone], 8)

or (maybe better) store just the digits:

Mid([phone], 2, 3) & Mid([phone], 7, 3) & Right([phone], 4)

and introduce the - with an Input Mask or Format property.

John W. Vinson[MVP]

 

[JamesJ]

I forgot to mention that the field type is Text. Should I change it to
Number??

James

Hi. I imported over 200 contacts into my Contacts table. The imported
data's phone number format is: (###) ###-####. I want to change it to:
###-###-####. Can someone please show me an update query to
change this format so I don't need to do each individual record?

Thanks,
James

You can use the Mid() function to extract the pieces. Try updating to
(test this first and back up your database!!!):

Mid([Phone], 2, 3) & "-" & Right([Phone], 8)

or (maybe better) store just the digits:

Mid([phone], 2, 3) & Mid([phone], 7, 3) & Right([phone], 4)

and introduce the - with an Input Mask or Format property.

John W. Vinson[MVP]

 

[Ken Snell [MVP]]

No, keep the Text format.
--

Ken Snell
<MS ACCESS MVP>

I forgot to mention that the field type is Text. Should I change it to
Number??

James

Hi. I imported over 200 contacts into my Contacts table. The imported
data's phone number format is: (###) ###-####. I want to change it to:
###-###-####. Can someone please show me an update query to
change this format so I don't need to do each individual record?

Thanks,
James

You can use the Mid() function to extract the pieces. Try updating to
(test this first and back up your database!!!):

Mid([Phone], 2, 3) & "-" & Right([Phone], 8)

or (maybe better) store just the digits:

Mid([phone], 2, 3) & Mid([phone], 7, 3) & Right([phone], 4)

and introduce the - with an Input Mask or Format property.

John W. Vinson[MVP]

 

[JamesJ]

The problem I'm having with the Input mask is that
I view my contacts in a list box and even though the
data shows ###-###-#### the list box displays it as
##########.

James

No, keep the Text format.
--

Ken Snell
<MS ACCESS MVP>

I forgot to mention that the field type is Text. Should I change it to
Number??

James

Hi. I imported over 200 contacts into my Contacts table. The imported
data's phone number format is: (###) ###-####. I want to change it to:
###-###-####. Can someone please show me an update query to
change this format so I don't need to do each individual record?

Thanks,
James


You can use the Mid() function to extract the pieces. Try updating to
(test this first and back up your database!!!):

Mid([Phone], 2, 3) & "-" & Right([Phone], 8)

or (maybe better) store just the digits:

Mid([phone], 2, 3) & Mid([phone], 7, 3) & Right([phone], 4)

and introduce the - with an Input Mask or Format property.

John W. Vinson[MVP]

 

[John Vinson]

The problem I'm having with the Input mask is that
I view my contacts in a list box and even though the
data shows ###-###-#### the list box displays it as
##########.

Use the Format() property in the listbox:

@@@-@@@-@@@@

John W. Vinson[MVP]

 

[JamesJ]

I wasn't aware the List Box control has a Format property.

James