updating a table

[Mark]

Hi all, I still need some assistance.

I have imported a table that has two columns, HospitalName
and HospitalPhone. Within the table there are several
hundred rows of data (records). 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 that are using
an input mask !\(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 told me to try:

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

but I am having trouble with this working. it is not
liking the Replace expression.

Thanks for the help.

 

[Mark]

I used the following in the field of a make table query:

HospitalPhone: "(" & Left([Hospitalphone],3) & ") " & Mid
([Hospitalphone],5,3) & "-" & Right([Hospitalphone],4)


It works!!!

 

[John Vinson]

Hi all, I still need some assistance.

I have imported a table that has two columns, HospitalName
and HospitalPhone. Within the table there are several
hundred rows of data (records). 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 that are using
an input mask !\(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.

Don't confuse the Format or the Mask with the value actually stored in
your table! Since you have a 0 as the second argument of your Input
Mask, it's actually storing the parentheses, blanks, and hyphens in
the Phone field. It sounds like you want to actually replace 555/ with
(555)_ - and do you actually want an underscore character, or a blank?

Let's say you want a blank (it's easy to change this), and that the
current ACTUAL VALUE STORED - i.e. that this field's Input mask
contains ;0; - you could run an Update query updating Phone to

"(" & Left([Phone], 3) & ") " & Mid([Phone], 5)

This will put in a literal ( and a literal ") ", and extract the first
three bytes - 555 in your example - and the portion of the number
after the fifth byte - 555-5555.