If this is your first visit, be sure to
check out the FAQ by clicking the
link above. You may have to register
before you can post: click the register link above to proceed. To start viewing messages,
select the forum that you want to visit from the selection below.

Phone number search

I'd like to add a way to search our Access database for a given phone
number. The problem is that everyone seems to enter phone numbers using
a different combination of "()", "-" , "+" and spaces. Is there an
easy way to strip away everything except the actual numberals to search
the phone field or do I need to write a loop to discard anything in the
string thats not numeric and then make my comparison for each record in
our database?

"Judy" <jcoffman@sos.net> wrote in message news:39AAFA42.2A37B04F@sos.net...
> I'd like to add a way to search our Access database for a given phone
> number. The problem is that everyone seems to enter phone numbers using
> a different combination of "()", "-" , "+" and spaces. Is there an
> easy way to strip away everything except the actual numberals to search
> the phone field or do I need to write a loop to discard anything in the
> string thats not numeric and then make my comparison for each record in
> our database?
>
> Thanks for any pointers!

Re: Phone number search

That IS pretty nifty! I still have approximately 6,000 phone numbers
already in the database that need correction so that they can also be
searched but in the meantime, this little function looks to be an easy
solution for the new records that get added in.

Re: Phone number search

Ok I think I missed the point of the original question but I am glad you can
use the ValiText Function.

Here is a way you can loop through all of the 6000 phone numbers and strip
out anything that is not numeric. (scrubbing) I won't write the actual code
but I can walk through the logic you can use.

1. Add a new field to the Access table to write the new scrubbed phone
numbers
2. Get a recordset in code from the table of the phone numbers
3. Enumerate through the recordset and run a scrubbing function on each
phone number
4. Update the recordset to set the new phone number field equal to the newly
scrubbed number.

This way we preserve the original numbers until you are satisfied that the
new ones are good. Then replace the original field with the new field.
(Delete the the original field, then rename the new field to the name of the
original)

Here is a sample of the scrubbiing function you can use: This will return a
scrubbed Phone number

Public function Scrubber (xPhoneNum as String) as String

'Get the length of the phone number and loop through each character
'Throw away the ones you don't want and save the ones that you do want
Dim I as Integer, x as String

'Start a For Loop to check each character of the phone Number
For I = 1 to Len(xPhoneNum)

'Pick out the next Character and set it equal to x
x = Mid(xPhoneNum,I,1)

'Evaluate if 'x' passes the numeric test, if it does add it to the new
scrubbed phone number
Select Case Isnumeric(x)
Case True
Scrubber = Scrubber & x
End Select
Next
End Function

"Judy" <jcoffman@sos.net> wrote in message news:39AAFA42.2A37B04F@sos.net...
> I'd like to add a way to search our Access database for a given phone
> number. The problem is that everyone seems to enter phone numbers using
> a different combination of "()", "-" , "+" and spaces. Is there an
> easy way to strip away everything except the actual numberals to search
> the phone field or do I need to write a loop to discard anything in the
> string thats not numeric and then make my comparison for each record in
> our database?
>
> Thanks for any pointers!

Re: Phone number search

Any confusion would be my fault. I had considered writing a loop to
detect and ignore any non-numeric characters WHEN doing a search (unless
of course there was a handy API that would do the trick). <g> I hadn't
thought of re-writing a second phone field for the whole database but
the way you explained it, it doesn't sound like such a BIG deal after
all. <g>

Thanks again for taking the time to consider (and explain) how you would
go about the same problem. I really appreciate the help!

Judy

Stever wrote:
>
> Ok I think I missed the point of the original question but I am glad you can
> use the ValiText Function.
>
> Here is a way you can loop through all of the 6000 phone numbers and strip
> out anything that is not numeric. (scrubbing) I won't write the actual code
> but I can walk through the logic you can use.
>
> 1. Add a new field to the Access table to write the new scrubbed phone
> numbers
> 2. Get a recordset in code from the table of the phone numbers
> 3. Enumerate through the recordset and run a scrubbing function on each
> phone number
> 4. Update the recordset to set the new phone number field equal to the newly
> scrubbed number.
>
> This way we preserve the original numbers until you are satisfied that the
> new ones are good. Then replace the original field with the new field.
> (Delete the the original field, then rename the new field to the name of the
> original)
>
> Here is a sample of the scrubbiing function you can use: This will return a
> scrubbed Phone number
>
> Public function Scrubber (xPhoneNum as String) as String
>
> 'Get the length of the phone number and loop through each character
> 'Throw away the ones you don't want and save the ones that you do want
> Dim I as Integer, x as String
>
> 'Start a For Loop to check each character of the phone Number
> For I = 1 to Len(xPhoneNum)
>
> 'Pick out the next Character and set it equal to x
> x = Mid(xPhoneNum,I,1)
>
> 'Evaluate if 'x' passes the numeric test, if it does add it to the new
> scrubbed phone number
> Select Case Isnumeric(x)
> Case True
> Scrubber = Scrubber & x
> End Select
> Next
> End Function
>
> "Judy" <jcoffman@sos.net> wrote in message news:39AAFA42.2A37B04F@sos.net...
> > I'd like to add a way to search our Access database for a given phone
> > number. The problem is that everyone seems to enter phone numbers using
> > a different combination of "()", "-" , "+" and spaces. Is there an
> > easy way to strip away everything except the actual numberals to search
> > the phone field or do I need to write a loop to discard anything in the
> > string thats not numeric and then make my comparison for each record in
> > our database?
> >
> > Thanks for any pointers!

Advertiser Disclosure:
Some of the products that appear on this site are from companies from which TechnologyAdvice receives compensation. This compensation may impact how and where products appear on this site including, for example, the order in which they appear. TechnologyAdvice does not include all companies or all types of products available in the marketplace.