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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
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
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

Dale FyeOwner, Developing Solutions LLCCommented:
how about something like:

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.