Sorting phone numbers by area codes and prefix

[Guest]

We have large database of business telephone numbers. We need to pull out
only those numbers from certain area codes and prefixes into there own list.
How do we do this?

Ex. We have a million numbers, but only want to pull those numbers that
have the first six digits we want to match. Also would like to enter a large
list of six digit numbers to scrub against instead of one at a time.

 

[Guest]

Hi Moses,

You can use the Left$ function to match the desired phone numbers. For
example, suppose I want to retrieve all phone numbers that begin with 206284
(I'm making the assumption that your phone numbers do not include stored
characters, such as parenthesis or hyphens, as in (206) 284- or 206-284-)

SELECT CustomerName, PhoneNumber
FROM tblCustomers
WHERE LEFT$([PhoneNumber],6) LIKE '206284' & "*"
ORDER BY CustomerName

Also would like to enter a large list of six digit numbers to scrub
against instead of one at a time.

Enter your large list into a new table. Then use a subquery. Something like
this:

SELECT LastName, FirstName, PhoneNumber
FROM tblCustomers
WHERE Left$([PhoneNumber],6)
In (SELECT TestNum FROM Scrublist)
ORDER BY Employees.LastName, Employees.FirstName;

where Scrublist is the name of the table containing the list of six digit
numbers that you wish to examine, in a field named TestNum.


Tom Wickerath
Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________