[AccessD] Search All Fields

Rocky,

    Sample procedure named P_BuildQueryBySearchOnAllFields() as given below, will build a query outputting the records containing search sub-string in any of the fields. Sample call would be as follows:

    P_BuildQueryBySearchOnAllFields "<<MyTable>>", _
                    "<<MySearchString>>", "<<MyDestnQuery>>"

    This query can then be used to set up a recordset as needed. However, if you are keen to set up the recordset directly, without simultaneously building a query, it can be done by using  sample function named Fn_GetRecordsetBySearchOnAllFields() as given below. Sample call would be as follows (rst being an object variable of DAO.Recordset type):

    Set rst = Fn_GetRecordsetBySearchOnAllFields( _
                    "<<MyTable>>", "<<MySearchString>>")

    An alternative style for handling the recordset generated by the sample function could be as follows:

' Code in VBA module
'=====================================
Sub TestFunction()
    With Fn_GetRecordsetBySearchOnAllFields( _
                "<<MyTable>>", "<<MySearchString>>")
        Debug.Print "Recordset Details:"
        Debug.Print "Tot Fields = " & .Fields.Count
        Debug.Print "First field values in output:"

        .MoveFirst
        Do Until .EOF
            Debug.Print .Fields(0)
            
            .MoveNext
        Loop
        
        Debug.Print "Tot records = " & .RecordCount
    End With
End Sub
'===================================

    If at any stage, you happen to need more complex search on all fields, my sample db named Form_SearchAllFields could be of interest to you. It is in Access 2000 file format and is available at Rogers Access Library. Link - 
http://www.rogersaccesslibrary.com/forum/forum_topics.asp?FID=45

    The sample db provides advanced search features across whole data source (all fields - all records). For example, for a group of space separated words typed by the user, search for matching field values could be conducted in any of the following styles:

    (a) Match field values having any of search words.
    (b) Match field values having all of search words, but not necessarily in the same order as entered in search box.
    (c) Match field values having all of search words, in the same order as entered in search box.
    (d) Exact match of  full field values against full contents of search box.

Best wishes,
A.D. Tejpal
------------

' Code in VBA module
'==============================
Sub P_BuildQueryBySearchOnAllFields( _
                            TableName As String, _
                            SearchString As String, _
                            DestnQryName As String)
    Dim fd As DAO.Field
    Dim Qst As String, Crit As String
    
    Qst = "SELECT * FROM " & TableName
    Crit = ""
    
    With DBEngine(0)(0)
        ' Build criteria
        For Each fd In .TableDefs(TableName).Fields
            Crit = Crit & IIf(Len(Crit) > 0, " OR ", "")
            Crit = Crit & fd.Name & " Like '*" & _
                        SearchString & "*'"
        Next
        
        ' Delete destn query if already existing by that name
        On Error Resume Next
            DoCmd.DeleteObject acQuery, DestnQryName
        On Error GoTo 0
        
        ' Create destn query
        Qst = Qst & IIf(Len(Crit) > 0, _
                " WHERE " & Crit, "") & ";"
        .CreateQueryDef DestnQryName, Qst
        .QueryDefs.Refresh
    End With

    Set fd = Nothing
End Sub
'---------------------------------------------------

Function Fn_GetRecordsetBySearchOnAllFields( _
        TableName As String, _
        SearchString As String) As DAO.Recordset
    Dim fd As DAO.Field
    Dim Qst As String, Crit As String
    
    Qst = "SELECT * FROM " & TableName
    Crit = ""
    
    With DBEngine(0)(0)
        ' Build criteria
        For Each fd In .TableDefs(TableName).Fields
            Crit = Crit & IIf(Len(Crit) > 0, " OR ", "")
            Crit = Crit & fd.Name & " Like '*" & _
                        SearchString & "*'"
        Next
        
        ' Build SQL
        Qst = Qst & IIf(Len(Crit) > 0, _
                " WHERE " & Crit, "") & ";"
        
        ' Set up recordset as per freshly built SQL
        Set Fn_GetRecordsetBySearchOnAllFields = _
                    .OpenRecordset(Qst, dbOpenDynaset)
    End With
    
    Set fd = Nothing
End Function
'===================================

  ----- Original Message ----- 
  From: Rocky Smolin 
  To: 'Access Developers discussion and problem solving' 
  Sent: Wednesday, December 23, 2009 02:24
  Subject: [AccessD] Search All Fields


  Dear List:

  A client wants to search for the occurrence of a string in ANY field of a
  table similar to what he does in datasheet view of a table.  But he wants
  all records with that string to end up in a recordset.  Is there a way to do
  that in a query?

  MTIA

  Rocky Smolin