Phone number search

[Judy]

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!

[Stever]

Hi Judy,

I found a nifty little function that you can use in Visual Basic to restrict
the the keys that are enetred into a text box that is called from the key
press event.

It is Called ValiText

Sub txtEdit_KeyPress(KeyAscii As Integer)
' Delete returns to get rid of beep.
If KeyAscii = 13 Then KeyAscii = 0
KeyAscii = ValiText(KeyAscii, "0123456789", True)
End Sub


Public Function ValiText(KeyIn As Integer, ValidateString As String,
Editable As Boolean) As Integer

Dim ValidateList As String
Dim KeyOut As Integer
'
If Editable = True Then
ValidateList = UCase(ValidateString) & Chr(8)
Else
ValidateList = UCase(ValidateString)
End If
'
If InStr(1, ValidateList, UCase(Chr(KeyIn)), 1) > 0 Then
KeyOut = KeyIn
Else
KeyOut = 0
'Beep
End If
'
ValiText = KeyOut
'
End Function


Enjoy this little snippet and modify it as you wish.

Stever

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

[Judy]

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.

Thanks!
Judy

[Stever]

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!

[Judy]

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!