Access: convert data type from text to a number HELP!!

[seanlivo81]

Ok I created a access database a long time ago, crm type database but due to poor decision making at the time I have stored phone numbers in a text data type but the data has come from a mask edit textbox in the format of: (00) 0000 0000

So I want to convert it to a plain number field but need to workout how?

Someone please help..

[jggtz]

IMO You could develop a conversion routine:
1.- Copy the table (now it's the old table)
2.- Change field data type to numeric in new table
3.- Read from old table/field
4.- Remove unwanted characters
5.- Write new table/field

Now, I have developed a lot of applications and always used telephone number field as string with no problem at all... it's only one more field of information

Let's wait the experts' opinion

JG

[seanlivo81]

if I want to just keep it as a string but remove all the formatting eg ( ) _ what sql command do I need to run to change them all.

if I run:

SELECT Replace(Replace(Replace(Replace(phone,"(",""),"_",""),")","")," ","") FROM tblClients;

it displays the number the way I want it to but how do I make the changes?

use the Update command?

[dee-u]

Something like this.

UPDATE tblClients SET phone =Replace(Replace(Replace(Replace(phone,"(",""),"_",""),")","")," ","")