Query Expression? 1

[rlgaooa]

I need to write a query that will do the following: extract the area code from the phone field and place it in another field without loosing the relationship to the phone number and the company name. The new database that I need to append the data to will only accept 7 digit phone numbers. So I need to take (555)123-4567 and turn it into two columns one that contains 555 without the parentheses and the other 1234567 without the dash. Where do I begin?

 

[lespaul]

what I would do is in your new table add a field to store the whole phone number (or in your old table add two fields to store the split and then transfer). If you can do that (even temporarily, you can delete the fields once your update is complete)

UPDATE your_table_name SET AREACODE = Mid$(WHOLE_PHONENUMBER,2,4), NEW_PHONENUMBER = Mid$(WHOLE_PHONENUMBER,6,8) + Mid$(WHOLE_PHONENUMBER,10,13)

might be another way, but this one will work (especially if you are just going to do this once)





Trim(Mid$(A.Name,1,Instr(1,A.Name,",")-1)), A.FirstName = Trim(Mid$(A.Name,Instr(1,A.Name,",")+1));

Leslie

 

[rlgaooa]

Thanks for the quick response, I ended up solving the problem by removing the linked tables, creating a new copy, adding new columns and using an update query to move the data to the new columns. Thanks for pointing me in the right direction.