• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 378
  • Last Modified:

Access query to analyse telephone number


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.

1 Solution
Jim Dettman (Microsoft MVP/ EE MVE)PresidentCommented:
You can use:


with a check of:

> 0

Jim Dettman (Microsoft MVP/ EE MVE)PresidentCommented:
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?

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.

Get 10% Off Your First Squarespace Website

Ready to showcase your work, publish content or promote your business online? With Squarespace’s award-winning templates and 24/7 customer service, getting started is simple. Head to Squarespace.com and use offer code ‘EXPERTS’ to get 10% off your first purchase.

Dale FyeCommented:
how about something like:

WHERE [phonenumber] Like "*[a-z,A-Z]*"
Rey Obrero (Capricorn1)Commented:
place this codes in a  regular module

function ValidateTelNo(strTel as string) as boolean

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

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


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

end function

to use in your query

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

Petermcg001Author Commented:
Thanks, works fine.  I added "!-/" so ended up with  "Like *[a-z,A-Z,!-/]*"
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.

Join & Write a Comment

Featured Post

Upgrade your Question Security!

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

Tackle projects and never again get stuck behind a technical roadblock.
Join Now