How do you Search Multiple fields.....??

Collapse
X
 
  • Time
  • Show
Clear All
new posts
  • IP This
    New Member
    • Jul 2007
    • 8

    How do you Search Multiple fields.....??

    Good Day Good People...

    I have a table with various fields, a number of fields relate to the same data type, i.e. Language1, Language2, Language3, Language4, - I want to be able to search all of the fields for e.g. Spanish.
    I currently use a combo list which is unbound, but without the user having to manually select each Language field to search in, i want the search to look in all of the Language fields automatically by only selecting, say "Language".

    Below is the code used to search the fields....
    Code:
    Private Sub cmdSearch_Click()
                
        If Len(cboSearchField) = 0 Or IsNull(cboSearchField) = True Then
            MsgBox "You must select a field to search."
            
        ElseIf Len(txtSearchString) = 0 Or IsNull(txtSearchString) = True Then
            MsgBox "You must enter a search string."
            
        Else
        
            'Generate search criteria
            GCriteria = cboSearchField.Value & " LIKE '*" & txtSearchString & "*'"
            
            'Filter frm_Report based on search criteria
            Form_frm_ReportCam.RecordSource = "select * from providers_cam where " & GCriteria
            Form_frm_ReportCam.Caption = "providers_cam (" & cboSearchField.Value & " contains '*" & txtSearchString & "*')"
            
            DoCmd.OpenForm "frm_ReportCam"
            
           'Close frm_SearchBoxCam
            DoCmd.Close acForm, "frm_SearchBoxCam"
            
        End If
        
    End Sub
    If you need more info will be glad to provide... if anyone has any ideas i will be, well, just glad.

    cheers.
    Last edited by JKing; Aug 29 '07, 12:43 PM. Reason: [CODE] Tags
  • ADezii
    Recognized Expert Expert
    • Apr 2006
    • 8834

    #2
    Originally posted by IP This
    Good Day Good People...

    I have a table with various fields, a number of fields relate to the same data type, i.e. Language1, Language2, Language3, Language4, - I want to be able to search all of the fields for e.g. Spanish.
    I currently use a combo list which is unbound, but without the user having to manually select each Language field to search in, i want the search to look in all of the Language fields automatically by only selecting, say "Language".

    Below is the code used to search the fields....

    ate Sub cmdSearch_Click ()

    If Len(cboSearchFi eld) = 0 Or IsNull(cboSearc hField) = True Then
    MsgBox "You must select a field to search."

    ElseIf Len(txtSearchSt ring) = 0 Or IsNull(txtSearc hString) = True Then
    MsgBox "You must enter a search string."

    Else

    'Generate search criteria
    GCriteria = cboSearchField. Value & " LIKE '*" & txtSearchString & "*'"

    'Filter frm_Report based on search criteria
    Form_frm_Report Cam.RecordSourc e = "select * from providers_cam where " & GCriteria
    Form_frm_Report Cam.Caption = "providers_ cam (" & cboSearchField. Value & " contains '*" & txtSearchString & "*')"

    DoCmd.OpenForm "frm_Report Cam"

    'Close frm_SearchBoxCa m
    DoCmd.Close acForm, "frm_SearchBoxC am"

    End If

    End Sub

    If you need more info will be glad to provide... if anyone has any ideas i will be, well, just glad.

    cheers.
    The following basic Code Template should provide the solution for you. It has been tested and is fully functional:
    [CODE=sql]Dim MySQL As String

    MySQL = "SELECT *FROM Providers_Cam WHERE Language1 Like " & "'*" & Me![txtSearchString] & "*'"
    MySQL = MySQL & " OR Language2 Like " & "'*" & Me![txtSearchString] & "*'"
    MySQL = MySQL & " OR Language3 Like " & "'*" & Me![txtSearchString] & "*'"
    MySQL = MySQL & " OR Language4 Like " & "'*" & Me![txtSearchString] & "*'"
    Me.RecordSource = MySQL[/CODE]

    Comment

    • IP This
      New Member
      • Jul 2007
      • 8

      #3
      Originally posted by ADezii
      The following basic Code Template should provide the solution for you. It has been tested and is fully functional:
      [CODE=sql]Dim MySQL As String

      MySQL = "SELECT *FROM Providers_Cam WHERE Language1 Like " & "'*" & Me![txtSearchString] & "*'"
      MySQL = MySQL & " OR Language2 Like " & "'*" & Me![txtSearchString] & "*'"
      MySQL = MySQL & " OR Language3 Like " & "'*" & Me![txtSearchString] & "*'"
      MySQL = MySQL & " OR Language4 Like " & "'*" & Me![txtSearchString] & "*'"
      Me.RecordSource = MySQL[/CODE]

      This is extremely useful... however just having trouble intergrating it into the original code... could you point me in the right direction.

      Thanks

      "The biggest problem encountered while trying to design a system that was completely foolproof, was, that people tended to underestimate the ingenuity of complete fools."

      Comment

      • ADezii
        Recognized Expert Expert
        • Apr 2006
        • 8834

        #4
        Originally posted by IP This
        This is extremely useful... however just having trouble intergrating it into the original code... could you point me in the right direction.

        Thanks

        "The biggest problem encountered while trying to design a system that was completely foolproof, was, that people tended to underestimate the ingenuity of complete fools."
        I'm sorry, but I seem a litlle confused and/or rusty trying to figure out exactly
        what you are searching for.
        1. What are some values contained in cboSearchField and how do they relate to txtSearchString ?
        2. What are some possible txtSearchString Field values?
        3. Write a typical search request indicating a value selected from cboSearchField, and an entry in txtSearchString .

        Comment

        Working...