locked
What's a good VBA phone search algorithm or query RRS feed

  • Question

  • I have no clue if there are better practices but here is what I am doing:

    When users enter a phone number for a new contact, I have VBA code manipulate the strings into these formats: (000) 000-0000, 000-000-0000, and 0000000000. Then I store each one into a different field: Phone1, DashPhone1, CompactPhone1.

    The idea is to let users search phone numbers without having to worry about the format they type the number in the search box. So when they input to search any of these formats, I have a field bound to a form which concatenates those 3 fields into one field for searching. Even more, this field doesn't just concatenate those 3 phone fields, but also [Company], [Phone2], and some others.

    The problem here is, [Phone2] doesn't have the extra DashPhone/CompactPhone fields like Phone1, so if users search for secondary phone number in one of those formats, it won't show up. I was going to create those new fields.. but I think I'm getting out of hand doing this the wrong way, so I am looking for advice or an example of a better solution.

    The search box is 1 textbox(I can create an advanced search singling out fields, but the quicksearch in 1 textbox is the goal here). This is Access & VBA.

    Thanks again!
    Thursday, December 10, 2015 2:54 PM

Answers

  • If you are using a mask when entering phone #'s, you can store the data without the mask, so you don't need any additional fields. If you are storing the formatting, then I would only use one additional field for each phone# (your CompactPhone).

    When users enter their criteria in the search box, strip out everything except the numbers (they don't need to see that) so you use only the numbers for your search. Otherwise, you could dig yourself a deep hole, trying to handle all of the different formats that users might enter.

    • Marked as answer by HTHP Friday, December 18, 2015 2:08 PM
    Thursday, December 10, 2015 3:54 PM

All replies

  • If you are using a mask when entering phone #'s, you can store the data without the mask, so you don't need any additional fields. If you are storing the formatting, then I would only use one additional field for each phone# (your CompactPhone).

    When users enter their criteria in the search box, strip out everything except the numbers (they don't need to see that) so you use only the numbers for your search. Otherwise, you could dig yourself a deep hole, trying to handle all of the different formats that users might enter.

    • Marked as answer by HTHP Friday, December 18, 2015 2:08 PM
    Thursday, December 10, 2015 3:54 PM
  • Hi. My take is that you don't need additional fields in the table if you can calculate the additional formats in a query. You can then use the query for your searching. Just my 2 cents...
    Thursday, December 10, 2015 4:10 PM
  • As I indicated, the single quick search textbox is used to search for more than just phone numbers. I am trying to maintain the ability for users to use this once single box to search through common fields like Company name, phone numbers, contact name, email address. So I'm not sure removing formatting would work if expected input is not exclusively a phone number.

    I'm kinda maked up what I was doing as I went, but it feels wrong. Would it be more efficient to store all data without format in 1 field, then just calculate and concatenate all possible versions of the format into a single field for searching each time someone searches?
    Thursday, December 10, 2015 4:35 PM
  • You should only have one field for a telephone number and it is user preference whether or not you store formatting characters in the field.  However, whether you choose to store your number with characters or without, you need to maintain consistency.  So it's one or the other, but not both.  For field size considerations, it's usually best not to store formatting characters.

    Keeping your field consistent means that you don't have to add special programming to manage variations in the number format.  Formatting is merely a visual convenience and can be accomplished using an input mask.  The mask will display the number in whatever format you like, but will ensure that the value is stored in a consistent manner.

    As for your search control, you can either supply the input mask for phone numbers or just let the user enter in and see only the digits.  Since you are trying to search a variety of fields from one control, you might consider adding a combo box for selection of the particular field to be searched, be it address, phone, or whatever.  Doing this will allow you to add an input mask to your search control when a phone number is the selected choice to search for.

     
    • Edited by RunningManHD Thursday, December 10, 2015 4:57 PM
    Thursday, December 10, 2015 4:56 PM
  • As I indicated, the single quick search textbox is used to search for more than just phone numbers. I am trying to maintain the ability for users to use this once single box to search through common fields like Company name, phone numbers, contact name, email address. So I'm not sure removing formatting would work if expected input is not exclusively a phone number.

    I'm kinda maked up what I was doing as I went, but it feels wrong. Would it be more efficient to store all data without format in 1 field, then just calculate and concatenate all possible versions of the format into a single field for searching each time someone searches?
    I've seen this technique before of concatenating all field values into one for searching, but I've always just search each individual field myself, so I never really ran into this issue before. How much trouble would it be for you to switch your technique now? Just curious...
    Thursday, December 10, 2015 5:19 PM