MS Access SQL Query to Seperate a Phone number column into three p

[Guest]

I have a phone number column in my Employee table, I am trying to find out
how to seperate it into three parts; AreaCode, Prefix, and Suffix. Once I
have the phone number seperated, I need to add a sub query to check to see if
the area codes and prefixes that come out of these phone numbers match the
area codes and prefixes in my AreaCode Table. John Vinson started to suggest
that I use an Update query without much detail, can anyone please help me
understand how to seperate this column using an SQL Query? I am using MS
Access 2003 and am not allowed to do this any other way than to write an SQL
query.
Thank You in Advance!

 

[Brendan Reynolds]

Well, what distinguishes the three parts of the number in the existing data?
For example are there hyphens, spaces, or some other delimiting character
between them?

--
Brendan Reynolds (MVP)
http://brenreyn.blogspot.com

The spammers and script-kiddies have succeeded in making it impossible for
me to use a real e-mail address in public newsgroups. E-mail replies to
this post will be deleted without being read. Any e-mail claiming to be
from brenreyn at indigo dot ie that is not digitally signed by me with a
GlobalSign digital certificate is a forgery and should be deleted without
being read. Follow-up questions should in general be posted to the
newsgroup, but if you have a good reason to send me e-mail, you'll find
a useable e-mail address at the URL above.

 

[Guest]

The phone number is currently set up as a TEXT field 10 characters long. The
is no format assigned to this field. Any help in writing an SQL query is
desperately needed.
Thanks,
Dan

 

[Douglas J. Steele]

Left([PhoneNumber], 3) will give you the area code.
Mid([PhoneNumber], 4, 3) will give you the exchange (prefix).
Right([PhoneNumber], 4) will give you the local (suffix)

 

[Guest]

Thank You Doug,
This worked! My update statement looks like...
UPDATE DC_Employee_Table SET DC_Employee_Table.DC_area_code =
Left([DC_home_phone_number],3), DC_Employee_Table.DC_prefix =
Mid([DC_home_phone_number],4,3), DC_Employee_Table.DC_suffix =
Right([DC_home_phone_number],4);
I don't know how to thank you all enough, this was a serious work out for me!
Dan

Left([PhoneNumber], 3) will give you the area code.
Mid([PhoneNumber], 4, 3) will give you the exchange (prefix).
Right([PhoneNumber], 4) will give you the local (suffix)

--
Doug Steele, Microsoft Access MVP

(No private e-mails, please)

The phone number is currently set up as a TEXT field 10 characters long. The
is no format assigned to this field. Any help in writing an SQL query is
desperately needed.
Thanks,
Dan

 

[Brendan Reynolds]

Lucky for you you're not in Ireland - area codes here can be two to four
digits long.

--
Brendan Reynolds (MVP)
http://brenreyn.blogspot.com

The spammers and script-kiddies have succeeded in making it impossible for
me to use a real e-mail address in public newsgroups. E-mail replies to
this post will be deleted without being read. Any e-mail claiming to be
from brenreyn at indigo dot ie that is not digitally signed by me with a
GlobalSign digital certificate is a forgery and should be deleted without
being read. Follow-up questions should in general be posted to the
newsgroup, but if you have a good reason to send me e-mail, you'll find
a useable e-mail address at the URL above.

Thank You Doug,
This worked! My update statement looks like...
UPDATE DC_Employee_Table SET DC_Employee_Table.DC_area_code =
Left([DC_home_phone_number],3), DC_Employee_Table.DC_prefix =
Mid([DC_home_phone_number],4,3), DC_Employee_Table.DC_suffix =
Right([DC_home_phone_number],4);
I don't know how to thank you all enough, this was a serious work out for
me!
Dan

Left([PhoneNumber], 3) will give you the area code.
Mid([PhoneNumber], 4, 3) will give you the exchange (prefix).
Right([PhoneNumber], 4) will give you the local (suffix)

--
Doug Steele, Microsoft Access MVP

(No private e-mails, please)

The phone number is currently set up as a TEXT field 10 characters
long. The
is no format assigned to this field. Any help in writing an SQL query
is
desperately needed.
Thanks,
Dan

:

Well, what distinguishes the three parts of the number in the
existing data?
For example are there hyphens, spaces, or some other delimiting character
between them?

--
Brendan Reynolds (MVP)
http://brenreyn.blogspot.com


I have a phone number column in my Employee table, I am trying to
find out
how to seperate it into three parts; AreaCode, Prefix, and Suffix. Once I
have the phone number seperated, I need to add a sub query to check
to see
if
the area codes and prefixes that come out of these phone numbers
match the
area codes and prefixes in my AreaCode Table. John Vinson started
to
suggest
that I use an Update query without much detail, can anyone please
help me
understand how to seperate this column using an SQL Query? I am
using MS
Access 2003 and am not allowed to do this any other way than to
write an
SQL
query.
Thank You in Advance!

 

[Tony Toews]

Lucky for you you're not in Ireland - area codes here can be two to four
digits long.

Actually anywhere outside North America.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm