Convert data.

[Robert]

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

 

[Andrew L.]

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.

 

[Guest]

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

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

Al

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

Rober

Keep your field as text, not numerical. If all your records are exactly the same length an
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 locate
a piece of text within another

ht
Andrew L