Convert data.

I have a table with phone number that were imported in the
following format (212)-555-0440. The field is set to text,
with no input masks.

I need to convert the numbers into a number field sothey
are stored as follows: 2125550440.

How would I formulate an Update query to do this
conversion?

Thanks

Robert

Keep your field as text, not numerical. If all your records are exactly the same length and
format:-

update to:Mid([yourfield],2,3) & Mid([yourfield],7,3) & Right([yourfield],4)

If your records are not exactly the same length/format, you will probably need Instr() which locates
a piece of text within another.

hth
Andrew L.

"Robert" wrote in message
...
I have a table with phone number that were imported in the
following format (212)-555-0440. The field is set to text,
with no input masks.

I need to convert the numbers into a number field sothey
are stored as follows: 2125550440.

How would I formulate an Update query to do this
conversion?

Thanks

Or, if you can't rely on the format being identical every time:

Replace(Replace(Replace(yourString,"(",""),")","") ,"-","")

Ali

----- Andrew L. wrote: -----

Robert

Keep your field as text, not numerical. If all your records are exactly the same length and
format:-

update to:Mid([yourfield],2,3) & Mid([yourfield],7,3) & Right([yourfield],4)

If your records are not exactly the same length/format, you will probably need Instr() which locates
a piece of text within another.

hth
Andrew L.

"Robert" wrote in message
...
I have a table with phone number that were imported in the
following format (212)-555-0440. The field is set to text,
with no input masks.
I need to convert the numbers into a number field sothey
are stored as follows: 2125550440.
How would I formulate an Update query to do this
conversion?
Thanks