Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
Solved

Telephone Number editing through query

Posted on 2003-03-12
Medium Priority
235 Views
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
Question by:bulldog317
• 5
• 4

LVL 1

Expert Comment

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

ID: 8120449
Or even...

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

...as I missed the hyphen.
0

Author Comment

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

LVL 1

Expert Comment

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

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

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

Author Comment

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

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")

0

Author Comment

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

Featured Post

Question has a verified solution.

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

Explore the ways to Unlock VBA Project Password Excel 2010 & 2013 documents. Go through the article and perform the steps carefully to remove VBA Excel .xls file.
When we develop an application in Ms Access 2016 we should also try to protect the queries, macros and table links. I know I may not have a permanent solution but for novice users, they will not manage to break your application. Below is the detail …
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…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…
Suggested Courses
Course of the Month10 days, 18 hours left to enroll