input mask issue

  • Thread starter Thread starter Mark
  • Start date Start date
M

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.
 
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],"-",""),"/",""),"(&&&)
&&&-&&&&");
 
Awesome!!! Thanks Wayne. That did the trick.

-----Original Message-----
Remove the input mask and run an update query. I just
Click to expand...
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.
Click to expand...


.
Click to expand...
 
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
Click to expand...
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.
Click to expand...


.
Click to expand...
.
Click to expand...
 
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
 
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


Mark said:
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
Click to expand...
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.


.
Click to expand...
.
Click to expand...
Click to expand...
 
Back
Top