Access query to analyse telephone number

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
LVL 1
Petermcg001Asked:
Who is Participating?
 
Dale FyeConnect With a Mentor Commented:
how about something like:

WHERE [phonenumber] Like "*[a-z,A-Z]*"
0
 
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
You can use:

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

with a check of:

> 0

Jim.
0
 
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
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
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

 
Petermcg001Author Commented:
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
 
Rey Obrero (Capricorn1)Commented:
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
 
Petermcg001Author Commented:
Thanks, works fine.  I added "!-/" so ended up with  "Like *[a-z,A-Z,!-/]*"
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.