Update Table Format

[fletchee2003]

Here is my problem:
Let's say that I have a table with a field name of "Phone Number". I input about 200 #'s in the following format: xxx-xxx-xxxx. I decide that I want to use the an input mask to create (xxx) xxx-xxxx. I input 50 more #'s. The previous 200 have the old format while the 50 recent #'s have the new format. How can I update the entire field from the old format to the new without reentering the data? Or can I? If I can, will the change also apply to any forms created off of this table?
Thanks

 

[KenHigg]

Do an update query and use something like:

myDate = "(" & left(mydate,3) & ") " & right(myDate,8)

Would this work?

Edit: And only update where left(myDate,1) <> "("

 

[fletchee2003]

hmmm...updat query

Thanks for the reply.

I am familiar with queries, but I have yet to do an update query. I have heard of it, but never performed one. If you could tell me the particulars, that would be great.

 

[KenHigg]

If you type in 'Update' in Access help, the first topic is 'Create an update query". That should get you going.

 

[fletchee2003]

got format to change but....

OK Ken,
I understand some of creating update queries and I pasted in your suggestion. It changed the format, but deleted all data in the process. Is it possible to make the changes apply without losing any data? Also, I am not real sure how you wrote the code, if there is some site that you know of where I could learn it, that would be a big help.

Thanks!

 

[KenHigg]

It really just done with what's called 'built in string functions'. Can you put enough of your db in an attachment? I'll see if I can work you up something...

 

[Pat Hartman]

Since you're going to update the data, remove all the formatting and change the mask so that it only stores data but not any of the formatting characters. So phonenumbers are stored as 1231231234.

 

[fletchee2003]

This was a problem that was brought up through a discussion I had with some friends. I don't actually have a large db yet. Just created one to try and solve this problem. So Ken, I could attach what I do have, but it just a table with the phone number field and about 3 entries. It you still want it, I will send it.

Thanks.