Solved

Access query to analyse telephone number

Posted on 2013-12-23
6
364 Views
Last Modified: 2013-12-23
Hello,

I need to run a query over a database with a telephone number field.

Some of these columns have had text added to them, such as "ext".

I need to find these but cannot use IsNumeric as many of the numbers have a space between the area code and the number.

Is there a function or expression which will show those columns where any of the characters are not either a number or a space.

Thanks
0
Comment
Question by:Petermcg001
[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
6 Comments
 
LVL 58
ID: 39735923
You can use:

 InStr([<field>],"ext")

with a check of:

> 0

Jim.
0
 
LVL 58
ID: 39735932
Sorry, focused on the extention.  

You can use Instr() to also search for  (, ), or +  (international calling).

That would be the simplest.  You could write a IsNumeric() type function to check the string byte by byte.

Is this a one time clean-up task or an on-going thing?

Jim.
0
 
LVL 1

Author Comment

by:Petermcg001
ID: 39735956
Hello Jim,

Thanks for looking at this.  Basically it could be any letter that has been used - people have been adding notes on the end of the number.  I don't think IsNumeric will work - for instance  the telephone number 0123 123456 is OK but isnumeric detects the space as a problem.

So basically I need a query that flags the record if the telephone number contains anything other than 0123456789 or a space.

It's a one-off.

Thanks
0
Technology Partners: 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!

 
LVL 48

Accepted Solution

by:
Dale Fye earned 500 total points
ID: 39736111
how about something like:

WHERE [phonenumber] Like "*[a-z,A-Z]*"
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 39736115
place this codes in a  regular module

function ValidateTelNo(strTel as string) as boolean

ValidateTelNo=True
dim j as integer, vtel as string, num as string
num=" 0123456789"

if strTel & ""="" then ValidateTelNo=false: exit function

vtel=strtel

for j= 1 to len(vtel)
     if instr(num, mid(vtel,j,1)) then
         'do nothing
         else
         'found a non numeric character
          ValidateTelNo=false
         exit for
      end if
next

end function


to use in your query

select [TelNo], ValidateTelNo([TelNo]) From tablename


.
0
 
LVL 1

Author Closing Comment

by:Petermcg001
ID: 39736197
Thanks, works fine.  I added "!-/" so ended up with  "Like *[a-z,A-Z,!-/]*"
0

Featured Post

On Demand Webinar: Networking for the Cloud Era

Ready to improve network connectivity? Watch this webinar to learn how SD-WANs and a one-click instant connect tool can boost provisions, deployment, and management of your cloud connection.

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…
Code that checks the QuickBooks schema table for non-updateable fields and then disables those controls on a form so users don't try to update them.
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

630 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