Telephone Number editing through query
Posted on 2003-03-12
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
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?