?
Solved

Telephone Number editing through query

Posted on 2003-03-12
9
Medium Priority
?
233 Views
Last Modified: 2011-10-03
I have two queries.  The first one looks like this (this query is called phone):

SELECT CLng(IIf(Len([callaccount])>7,Left(CStr([callaccount]),2),Left(CStr([callaccount]),1))) AS AuthBy, Format([ArchData].[CallStartTime],"yyyymmdd") AS MyDate, CLng(IIf(Len([callaccount])=7,Mid(CStr([callaccount]),2,3),Mid(CStr([callaccount]),3,3))) AS Client, CLng(IIf(Len([callaccount])=9,Right(CStr([callaccount]),4),Right(CStr([callaccount]),3))) AS Matter, round(([CallDurationSec]/60)+0.5)*0.1 AS MyNumber, "Long Distance Call" & " " & callingnumber AS Narrative
FROM archdata
WHERE (((archdata.callaccount)>0));

The second query then appends the info to the table and looks like this:

INSERT INTO LongD ( AuthBy, [date], Client, Matter, Units, Narrative )
SELECT UID.Textcode, phone.MyDate, CID.Textcode, phone.matter, phone.MyNumber, phone.Narrative
FROM (phone INNER JOIN UID ON phone.AuthBy = UID.numcode) INNER JOIN CID ON phone.Client = CID.numcode;

Once complete, the Narrative field of my table looks something like this:

Long Distance Call 2345678998898

How do I take the number following Long Distance Call (which is a telephone number) and delete the extra numbers at the end so that I only have ten digits and then turn it into (234) 567-8998?

Also, how would I query the first three numbers of the telephone number (which is the area code) to see if it is my local area code and, if so, exclude it from being added?
0
Comment
Question by:bulldog317
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 4
9 Comments
 
LVL 1

Expert Comment

by:jonsykes
ID: 8120437
As you know that you want to ignore long distance call and that is a constant value, try something like:

? format(mid(FieldName,20,10),"(000) 000 0000")
0
 
LVL 1

Expert Comment

by:jonsykes
ID: 8120449
Or even...

? format(mid(FieldName,20,10),"(000) 000-0000")

...as I missed the hyphen.
0
 

Author Comment

by:bulldog317
ID: 8120535
It would not take the query with the ? in it.  Excluding the ?, when I ran the second query, out of 29 records, only one number transfered over.  Here is the query with your addition:

SELECT CLng(IIf(Len([callaccount])>7,Left(CStr([callaccount]),2),Left(CStr([callaccount]),1))) AS AuthBy, Format([ArchData].[CallStartTime],"yyyymmdd") AS MyDate, CLng(IIf(Len([callaccount])=7,Mid(CStr([callaccount]),2,3),Mid(CStr([callaccount]),3,3))) AS Client, CLng(IIf(Len([callaccount])=9,Right(CStr([callaccount]),4),Right(CStr([callaccount]),3))) AS Matter, round(([CallDurationSec]/60)+0.5)*0.1 AS MyNumber, "Long Distance Call" & format(mid(callingnumber,20,10)," (615) 000-0000") AS Narrative
FROM archdata
WHERE (((archdata.callaccount)>0));
0
Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 
LVL 1

Expert Comment

by:jonsykes
ID: 8120576
Sorry, I copied from debug window and included the ? by mistake.

As for your problem, what you need to do is change the 20, 10 to 1,10. I used 20 as that would begin the extraction of the phone number at character 20 ("Long Distance Call " is 19 characters in length). Also, don't use (615), use (000) as this is the format character for numbers including any leading zeros.
0
 

Author Comment

by:bulldog317
ID: 8120640
Sorry. I tried 000 at first and then tried 615 just to see if it made a difference.  Changing the 20 to a 1 worked correctly.  Now how do I exclude a number if the first three numbers (the area code) equal, for instance, 615?  Thanks jonsykes!
0
 
LVL 1

Accepted Solution

by:
jonsykes earned 400 total points
ID: 8120658
try
WHERE (((archdata.callaccount)>0)) AND mid(callingnumber,1,3) <> "615";
0
 

Author Comment

by:bulldog317
ID: 8120713
Perfect!  If I wanted to exclude other numbers besides 615, do I just add the same AND statement or is there an easier way to include for instance 888 to that same AND statement?  Thanks a ton for your help jonsykes!
0
 
LVL 1

Expert Comment

by:jonsykes
ID: 8120757
Depends. If you're wanting to exclude 1 or 2 codes, you can just add extra AND statements.

To exclude more than that, it may be more efficient to use

mid(callingnumber,1,3) NOT IN ("615", "888", "123", "555")

And add to that list.
0
 

Author Comment

by:bulldog317
ID: 8120793
It would be six numbers so the NOT IN works perfectly.  Thanks again!
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
Suggested Courses

765 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