Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 374
  • Last Modified:

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
0
Petermcg001
Asked:
Petermcg001
1 Solution
 
Jim Dettman (Microsoft MVP/ EE MVE)PresidentCommented:
You can use:

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

with a check of:

> 0

Jim.
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?

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
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!

 
Dale FyeCommented:
how about something like:

WHERE [phonenumber] Like "*[a-z,A-Z]*"
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

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

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