Solved

Split 10 digit phone numbers into 2 fields

Posted on 2002-04-06
5
279 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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Regardless of which version on MS Access you are using, one of the harder data-entry forms to create is one where most data from previous entries needs to be appended to new records, especially when there are numerous fields and records involved.  W…
Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…

744 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now