input mask issue

[Mark]

Hi,

I have a table with two columns, HospitalName and
HospitalPhone. Within the table there are several hundred
rows of data. The current format of hospital phone number
is like: 555/555-5555. Is there a way to convert the
format of all the phone numbers in the table to: (555)_555-
5555? So I can consistently use the same format as with
other fields in my form. The Input Mask that I have been
using is: !\(999") "000\-0000;0;_

I am thinking that there should be a way I can accomplish
this using a query, perhaps a make-table query? A query
with criteria that will convert the current format to the
format I am trying to get too like: (555)_555-5555

Thanks for taking a look at this! I am unsure of how to
write the criteria to do this.

 

[Wayne Morgan]

Remove the input mask and run an update query. I just tried this update query on a test
table and it changed

555/555-5555
to
(555) 555-5555


UPDATE Table1 SET Table1.Phone = Format(Replace(Replace([Phone],"-",""),"/",""),"(&&&)
&&&-&&&&");

 

[Mark]

Awesome!!! Thanks Wayne. That did the trick.

-----Original Message-----
Remove the input mask and run an update query. I just

tried this update query on a test

table and it changed

555/555-5555
to
(555) 555-5555


UPDATE Table1 SET Table1.Phone = Format(Replace(Replace ([Phone],"-",""),"/",""),"(&&&)
&&&-&&&&");


--
Wayne Morgan
Microsoft Access MVP

Hi,

I have a table with two columns, HospitalName and
HospitalPhone. Within the table there are several hundred
rows of data. The current format of hospital phone number
is like: 555/555-5555. Is there a way to convert the
format of all the phone numbers in the table to: (555) _555-
5555? So I can consistently use the same format as with
other fields in my form. The Input Mask that I have been
using is: !\(999") "000\-0000;0;_

I am thinking that there should be a way I can accomplish
this using a query, perhaps a make-table query? A query
with criteria that will convert the current format to the
format I am trying to get too like: (555)_555-5555

Thanks for taking a look at this! I am unsure of how to
write the criteria to do this.

.

 

[Mark]

Wayne, I spoke too soon. I am having trouble getting this
to work on my end. I know it is something very simple.
Can you walk me through the process you went through?
Thanks, Mark

-----Original Message-----
Awesome!!! Thanks Wayne. That did the trick.

-----Original Message-----
Remove the input mask and run an update query. I just

tried this update query on a test

table and it changed

555/555-5555
to
(555) 555-5555


UPDATE Table1 SET Table1.Phone = Format(Replace(Replace ([Phone],"-",""),"/",""),"(&&&)
&&&-&&&&");


--
Wayne Morgan
Microsoft Access MVP

Hi,

I have a table with two columns, HospitalName and
HospitalPhone. Within the table there are several hundred
rows of data. The current format of hospital phone number
is like: 555/555-5555. Is there a way to convert the
format of all the phone numbers in the table to: (555) _555-
5555? So I can consistently use the same format as with
other fields in my form. The Input Mask that I have been
using is: !\(999") "000\-0000;0;_

I am thinking that there should be a way I can accomplish
this using a query, perhaps a make-table query? A query
with criteria that will convert the current format to the
format I am trying to get too like: (555)_555-5555

Thanks for taking a look at this! I am unsure of how to
write the criteria to do this.

.

.

 

[Jim/Chris]

Can't you opent the table and do an edit/replace to remove
all spaces and special characters? Once you have done that
your input mask will preveint user from entering anything
but numbers.

Jim

 

[Wayne Morgan]

1) I created a table with a text field and put in the phone number as you had it.
2) I removed the input mask so that it wouldn't cause me a problem as I changed the data.
3) I created the query.
4) I ran the query.
5) I created the new input mask.

One thing I may have missed, because I just left it at the default of 50 for the test. You
old field size only need to be 12 characters, the new one will need to be 14. Make the
change before you run the query.

--
Wayne Morgan
Microsoft Access MVP

Wayne, I spoke too soon. I am having trouble getting this
to work on my end. I know it is something very simple.
Can you walk me through the process you went through?
Thanks, Mark

-----Original Message-----
Awesome!!! Thanks Wayne. That did the trick.

-----Original Message-----
Remove the input mask and run an update query. I just

tried this update query on a test

table and it changed

555/555-5555
to
(555) 555-5555


UPDATE Table1 SET Table1.Phone = Format(Replace(Replace ([Phone],"-",""),"/",""),"(&&&)
&&&-&&&&");


--
Wayne Morgan
Microsoft Access MVP


Hi,

I have a table with two columns, HospitalName and
HospitalPhone. Within the table there are several hundred
rows of data. The current format of hospital phone number
is like: 555/555-5555. Is there a way to convert the
format of all the phone numbers in the table to: (555) _555-
5555? So I can consistently use the same format as with
other fields in my form. The Input Mask that I have been
using is: !\(999") "000\-0000;0;_

I am thinking that there should be a way I can accomplish
this using a query, perhaps a make-table query? A query
with criteria that will convert the current format to the
format I am trying to get too like: (555)_555-5555

Thanks for taking a look at this! I am unsure of how to
write the criteria to do this.


.

.