D
You are using an out of date browser. It may not display this or other websites correctly.
You should upgrade or use an alternative browser.
You should upgrade or use an alternative browser.
J
John Vinson
On Mon, 27 Sep 2004 16:51:01 -0700, "DC"
By using the Left(), Mid(), and Instr() functions in an Update query.
For a more detailed answer, please post a more informative question -
*as a message*, not as the subject line of a message.
John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps
By using the Left(), Mid(), and Instr() functions in an Update query.
For a more detailed answer, please post a more informative question -
*as a message*, not as the subject line of a message.
John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps
D
Dan
John, Thank you for staring to get me on the right track, this is my first
time asking for help, or more like begging for mercy! I have been going nuts
trying to seperate my phone number column into areacode, prefix, and suffix.
I have spent all night trying to get the INSERT INTO command to work for me
since I have to compare the seperated area codes and prefixes that I am
trying to create in my employees table with the area codes and prefixes in my
Area Code table. Any further guidance about how to use the update to: Create
three new fields inmy employees table while comparing to my Area code table
with a sub query will be enormously appreciated.
Thanks Again,
Dan
time asking for help, or more like begging for mercy! I have been going nuts
trying to seperate my phone number column into areacode, prefix, and suffix.
I have spent all night trying to get the INSERT INTO command to work for me
since I have to compare the seperated area codes and prefixes that I am
trying to create in my employees table with the area codes and prefixes in my
Area Code table. Any further guidance about how to use the update to: Create
three new fields inmy employees table while comparing to my Area code table
with a sub query will be enormously appreciated.
Thanks Again,
Dan
J
John Vinson
John, Thank you for staring to get me on the right track, this is my first
time asking for help, or more like begging for mercy! I have been going nuts
trying to seperate my phone number column into areacode, prefix, and suffix.
I have spent all night trying to get the INSERT INTO command to work for me
since I have to compare the seperated area codes and prefixes that I am
trying to create in my employees table with the area codes and prefixes in my
Area Code table. Any further guidance about how to use the update to: Create
three new fields inmy employees table while comparing to my Area code table
with a sub query will be enormously appreciated.
Thanks Again,
Dan
Thanks! This helps a great deal. Remember, we can't see your database
- having some more detail is very useful.
If you have a Phone field like (313)555-5555 and you really want three
fields - 313, 555, and 5555 - the simplest way to do it would be to
use an Update query (not an Insert Into query, which creates new
records where none existed before). (I'm not sure what benefit you
would get from separating the prefix and suffix, but if that's what
you want...)
Open your table in design view and add the Areacode, Prefix, and
Suffix fields. They'll be empty of course. Be sure that they are of
Text datatype, 3, 3, and 4 bytes size respectively. Just to be
complete, maybe add an Extension field too.
Create an Update query based on your table. On the UpdateTo line under
Areacode, Prefix, and Suffix put
Mid([Phone], 2, 3)
Mid([Phone], 6, 3)
Right([Phone], 4)
respectively - adjust for your fieldnames and the format of your phone
number.
Run the query. Check your results.
You should be able to then join the Areacode table's Areacode field to
this areacode field.
John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps