dcsimg


DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 5 of 5

Thread: Phone number search

  1. #1
    Judy Guest

    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?

    Thanks for any pointers!

  2. #2
    Stever Guest

    Re: Phone number search

    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!




  3. #3
    Judy Guest

    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.

    Thanks!
    Judy

  4. #4
    Stever Guest

    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!




  5. #5
    Judy Guest

    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!


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center
 
 
FAQ
Latest Articles
Java
.NET
XML
Database
Enterprise
Questions? Contact us.
C++
Web Development
Wireless
Latest Tips
Open Source


   Development Centers

   -- Android Development Center
   -- Cloud Development Project Center
   -- HTML5 Development Center
   -- Windows Mobile Development Center