Query to Perform Phone Number Searches

[sunnylion810]

I am working with a table that contains 4 fields: NPA(Area code), NXX(phone # exchange), Low Range and High Range (last 4 digits of the phone number. The table is designed with range fields due to the fact that in the telecommunications field most of the numbers that we provide to customers are DIDs in sequential ranges. But, occassionally we need to verify which customer a number belongs to. How do I write a query, preferably via SQL statements that will allow me to query who this number: 323-795-1407, for example belongs to without manually scrolling through the table to find the range? I'd like a validation statement to generate that, "323-795-1407 is currently assigned to XCustomer within the range of X (beginning and end of range)".

 

[MichaelRed]

Is the table restricted to the "U.S." Style of Phone #'s?

The area code and exchange need to match so ~~

"Select * from YourTable "

"[NPA] = Your ArCode and [NXX] = YourExchge "

The four digits need to be checked aginst both the high and low ranges so add this to the criteria

&quot;and ([Low Range] >= YourLast and [High Range] <= YourLast);&quot;

You need to replace all the Your* pieces with the appropiate variables from your app / system. I would ASSUME the table name renains the same, and each part of the phone # is entered seperatly (otherwise you need to parse the pieces out individually), and I FURTHER assume that the resultsset is returned to a control (or controls?} on your form / report.

As far as validating the number is assigned, you do not provide sufficient info for me to do this, however IF there is a &quot;Customer&quot; field, then just returning it (&quot;Customer&quot would verify it for you. Of course, many details are missing, but then the request does not provide sufficient info to deal with the entire app / procedure.

MichaelRed
m.red@att.net

There is never time to do it right but there is always time to do it over