Results 1 to 3 of 3
  1. #1
    Nexus13 is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Jul 2011
    Posts
    10

    Search across multiple fields

    Just need a quick pointer on my search setup.

    I have a Query called 'SearchQ'.
    Within it there are 8 different fields listing each customer's information.

    My search Form 'SearchF' has:
    1.) A textbox called 'Phonesearch' for inputting the value to be searched
    2.) A listbox called 'SearchCustomerResultsListBox' for displaying the search results
    3.) a button for starting the search

    Here is my code after clicking on the "search button" next to the input field for the search.

    Private Sub Command0_Click()

    Dim W As String
    Dim S As String
    W = ""
    S = ""
    If Not IsNull(PhoneSearch) Then


    W = " PhoneCell LIKE ""*" & PhoneSearch & "*"" "
    End If

    S = "SELECT [CustomerID],[FirstName], [LastName], [PhoneCell], [PhoneHome], [PhoneWork],[EmailPersonal], [EmailWork] FROM SearchCustomerQ "

    If W <> "" Then
    S = S & " WHERE " & W
    End If
    S = S & "ORDER BY [LastName], [FirstName]; "

    SearchCustomerResultsListBox.RowSource = S

    End Sub


    I'd like the Code to search not just the 'PhoneCell' directory but also the 'PhoneHome' and 'PhoneWork' sections as well.

    Thanks in advance!

  2. #2
    boblarson is offline --------
    Windows 7 64bit Access 2010 32bit
    Join Date
    Jun 2011
    Posts
    1,272
    The easiest way is to concatenate the three phone fields like:

    MyNewField:[PhoneCell] & [PhoneHome] & [PhoneWork]

    and then the criteria would be

    Like "*" & [Forms]![YourFormNameHere]![PhoneSearch] & "*"

  3. #3
    Nexus13 is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Jul 2011
    Posts
    10
    That works fantastic!

    thanks Bob.

    one question further:

    My phone numbers are all stored in this format 1234567890 in my tables.

    I am using masks for inputs on my forms to make them look more appealing. e.g. (123) 456-7890

    When my searchListbox pulls all the values from the search query, it is displaying the phone numbers as they in the table.

    Is there a way to make them appear like (123) 456-7890 in my listbox?
    If so, would this display mask go on the listbox itself or on the query that is supplying the listbox?

    thanks for your help!

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Help with Search Fields
    By jhawk in forum Forms
    Replies: 2
    Last Post: 03-16-2011, 08:42 AM
  2. Help with Search Fields
    By jhawk in forum Programming
    Replies: 0
    Last Post: 03-15-2011, 08:05 AM
  3. combo box to search multiple fields
    By jo15765 in forum Forms
    Replies: 21
    Last Post: 12-23-2010, 03:28 PM
  4. Replies: 4
    Last Post: 09-22-2010, 01:47 AM
  5. Replies: 1
    Last Post: 12-10-2009, 08:41 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums