updating a table

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

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

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.
 
Back
Top