Searching multiple Fields in a table.

[camaroltd82]

I have a dB that I designed last June. It is a program that holds product information in a storeroom. My users have requested a way to search the ENTIRE dB, with only one search string.

I have one main table that contains my parts data, and I have select queries that control all my searches through each individual field that my user selects. Is there a way to make a select query that will prompt the user for ONE search string, and then look for that string ANYWHERE in the table, not just in a specific field? Thanks in advance


Mike

 

[Jack Cowley]

Dim db As DAO.Database
Dim QD As DAO.QueryDef
Dim where As Variant

Set db = CurrentDb()
'Delete the existing dynamic query; trap the error if the query does not exist.
On Error Resume Next
db.QueryDefs.Delete ("SearchQuery")

where = Null

where = where & " Or [Field1]='" + Me![Keyword] + "'"
where = where & " Or [Field2]='" + Me![Keyword] + "'"
where = where & " Or [Field3]='" + Me![Keyword] + "'"


Set QD = db.CreateQueryDef("SearchQuery", _
"Select * from MyTable " & (" where " + Mid(where, 4) & ";"))

DoCmd.OpenForm "frmSearchResults"

Change Field1, etc. to the names of fields on your form. If you are using Access2000 or higher be sure and select the DAO library. Base your SearchForm results on SearchQuery. Add as many 'where' clauses as you need. You will need to change the syntax in the 'where' clauses for date and number Data Types.

For more information about this method see the article below:

Query by form

hth,
Jack

 

[llkhoutx]

I think that you're going to have to resort to vba code; inputing the search string via a text box and then build you query "OR'ing" each filed to be searched.

By way of example, your record source would become:

me.recordsource="SELECT tbTest.ID, tbTest.s1, tbTest.s2, tbTest.s3, tbTest.s4
FROM tbTest
WHERE (((tbTest.s1) Like ""*" & Forms!YourFormName!txtFilter & "*""" & “)) OR (((tbTest.s2) Like ""*" & Forms!YourFormName!txtFilter & "*""" & “)) OR (((tbTest.s3) Like ""*" & Forms!YourFormName!txtFilter & "*""" & “)) OR (((tbTest.s4) Like ""*" & Forms!YourFormName!txtFilter & "*""" & “));"
me.requery

Note the double quotes on the outside of the * outside Forms!YourFormName!txtFilter.

Note that the table for the above has fields, ID, s1, s2, s3 & s4.

 

[raskew]

The following will search all text and memo fields for the
presence of a user-specified string and respond with a
query showing the location of each occurrence.

As written (for Access 97), it does require the table to have a primary key.

Hopefully it will prove a little more dynamic since the
user isn't required to specify the field names.

To test, copy the following into a new module in Northwind
then test from the debug window with:

? stringfind("Products", "ot")

Function StringFind(ptblName As String, pStrToFind As String)
 '*******************************************
 'Name: StringFind (Function)
 'Purpose: Search all text and memo fields
 ' for specific string
 'Inputs: in Northwind debug.window
 ' ? stringfind("Products", "ot")
 'Output: All records containing "ot"
 '*******************************************

 Dim db As Database, rs As Recordset, tblName As String
 Dim test As String, strSQL As String, fld As Field
 Dim primHold As String, primType As String, qd As QueryDef, fldHold As String

 Set db = CurrentDb
 ' Trap for any errors.
 On Error Resume Next
 tblName = "z_tblTempHold"
 'Does table tblname exist? If true, delete it;

 test = db.TableDefs(tblName).Name
 If Err <> 3265 Then 'the specified table exists
 DoCmd.DeleteObject acTable, tblName
 End If

 'Find name of primary key
 primHold = PrimKey(ptblName)

 'SQL string to create new table

 strSQL = "CREATE TABLE " _
 & tblName _
 & "(ThePrim INTEGER, TheField Text); "

' & "(ThePrim INTEGER CONSTRAINT MyFieldConstraint " _
' & "PRIMARY KEY, TheField Text); "
  
 'create the table
 db.Execute strSQL

 'open target table

 Set rs = db.OpenRecordset(ptblName, dbOpenDynaset)

 'create query for each text and memo field
 For Each fld In rs.Fields

 If fld.Name <> primHold And (fld.Type = 10 Or fld.Type = 12) Then
 fldHold = fld.Name
 strSQL = "INSERT INTO " & tblName & " ( ThePrim, TheField ) " _
 & "SELECT " & primHold & ", " & "'" & fldHold & "' FROM " & ptblName & " WHERE " _
 & "(((Instr([" & fld.Name & "], '" & pStrToFind & "'))>0));"
 db.Execute strSQL
 End If
 Next fld
 rs.Close

 'get field names found
 fldHold = StringEm(tblName, "theField")

  'create select query SQL for records selected
  'limit fields displayed to only those found

  strSQL = "SELECT " & primHold & ", TheField AS WhereFound, " & fldHold _
    & " FROM " & ptblName & " INNER JOIN " & tblName & " ON " & ptblName _
    & "." & primHold & "= " & tblName & ".ThePrim" _
    & " ORDER BY z_tblTempHold.ThePrim;"
 
 'create query def
 Set qd = Nothing
 DoCmd.DeleteObject acQuery, "queryXXX"
 Set qd = db.CreateQueryDef("queryXXX", strSQL)
 db.QueryDefs.Refresh

 'run the query
 DoCmd.OpenQuery "queryXXX", acViewNormal

 db.Close
 End Function

 Function PrimKey(tblName As String)
 '*******************************************
 'Name: primkey (Function)
 'Purpose: Programatically determine a
 ' table's primary key
 'Author: rAskew
 'Inputs: from Northwind's debug window:
 ' ? PrimKey("Products")
 'Output: "ProductID"
 '*******************************************

 Dim db As Database
 Dim td As TableDef
 Dim idxLoop As Index

 Set db = CurrentDb
 Set td = db.TableDefs(tblName)
 For Each idxLoop In td.Indexes
 If idxLoop.Primary = True Then
 PrimKey = Mid(idxLoop.Fields, 2)
 Exit For
 End If
 Next idxLoop

 db.Close
 Set db = Nothing

 End Function

Function StringEm(ptblName As String, pfldName As String) As String
'*******************************************
'Name:      StringEm (Function)
'Purpose:   Build a string of field names
'           displayed in a record set
'Author:    raskew
'Called by: Function StringFind()
'*******************************************

Dim db As Database, rs As Recordset
Dim strSQL As String, strHold As String
Dim n As Integer, i As Integer

Set db = CurrentDb
strSQL = "SELECT distinct " & pfldName & " FROM " & ptblName _
& " ORDER BY " & pfldName & "; "
Set rs = db.OpenRecordset(strSQL)
rs.MoveLast
n = rs.RecordCount
rs.MoveFirst
If n > 0 Then
  For i = 1 To n
     strHold = strHold & rs(pfldName) & ", "
     rs.MoveNext
  Next i
  strHold = Left(strHold, Len(Trim(strHold)) - 1)
  StringEm = strHold
End If
rs.Close
db.Close
Set db = Nothing
End Function