 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / Queries / November 2007Update query for area codes
I am working on a very old database where the area code was not included with the telephone number as all of the area codes were the same. I recently had to add the ( ) to accommodate a new area code. What I would like to do is include the area code 401 to all of the phone numbers where the area code has not been filled in. Can someone help me with this? Thank you, > I am working on a very old database where the area code was not included with > the telephone number as all of the area codes were the same. I recently had [quoted text clipped - 9 lines] > -- > Message posted viahttp://www.accessmonster.com I would recommend an update query using a if statement in the update field as listed below. iif(len([PhoneNo])=7, "401" & [PhoneNo], [PhoneNo]) Basically, you check is the length of the phone number is 7 (does not have an area code). If it does not have an area code then you concatinate the Area code else you leave it the same. The exact way you do this is dependant on how the number is stored. Is your phone number stored as a numeric value or a string. Is the formating (ie '(###) ###-####' ) used or is the user actually entering the formating? MAC >The exact way you do this is dependant on how the number is stored. >Is your phone number [quoted text clipped - 3 lines] > >MAC The number is stored as a numeric value. The formating is (###) ###-####. Let me give this IF Statement a try. >The number is stored as a numeric value. The formating is (###) ###-####. >Let me give this IF Statement a try. Change it to Text... you'll never be doing arithmatic with phone numbers, and Long Integers are limited to 2147483647 so any phone numbers in area codes 215 and up will give you an error message. John W. Vinson [MVP] |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.