Solved

Split 10 digit phone numbers into 2 fields

Posted on 2002-04-06
5
305 Views
Last Modified: 2008-03-06
I need help with a query to run in Access. I have a field called "phone" in a table called "NewMasterName". This contains phone numbers in two different formats some with area code and some without. I need to split these.

one is 1112223333
the other is 2223333

I need to break this down in this way, if the field contains only the 7 digit phone number leave it alone, BUT if it contains a 10 digit phone number I need to pull the first three digits and move them into a field called "AreaCode" in the same table. Can somebody give me a simple query to do this?
0
Comment
Question by:DavidNPD
  • 3
5 Comments
 
LVL 1

Accepted Solution

by:
innosys earned 50 total points
ID: 6923211
you can use an update query to do this
UPDATE NewMasterName SET NewMasterName.phone = IIf(Len([phone])=10,Mid([phone],4,10),[phone]), NewMasterName.AreaCode = IIf(Len([phone])=10,Mid([phone],1,3),"");
0
 
LVL 5

Expert Comment

by:hafeezmca
ID: 6923443
Try out this simple procedure

UPDATE NewMasterName SET NewMasterName.phone = IIf(Len([phone])=10,right([phone],7),[phone]), NewMasterName.AreaCode
= IIf(Len([phone])=10,left([phone],3),"");
0
 

Author Comment

by:DavidNPD
ID: 6923997
I have tried both of these without success, I changed both fields to the text type and when I run the query I get an error that "could not update due to validation rule violations"  Could this be because any of the fields that are empty have a "0"  (zero) in them? I changed both fields back to the Number type and then I get an error that it couldnt update because of a type conversion error. Any ideas?
0
 

Author Comment

by:DavidNPD
ID: 6924021
I have tried both of these without success, I changed both fields to the text type and when I run the query I get an error that "could not update due to validation rule violations"  Could this be because any of the fields that are empty have a "0"  (zero) in them? I changed both fields back to the Number type and then I get an error that it couldnt update because of a type conversion error. Any ideas?
0
 

Author Comment

by:DavidNPD
ID: 6924024
innosys, My mistake, this did work, I thought it hadnt when I saw the errors, Thanks for your help.
hafeezmca, thanks for your reply, your query would have worked as well.
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

808 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question