Search all fields 1

[kermit01de]

Is there any possibility to

have a search-field to enter something and

to have a result list that diplays all records that match the entered text in ANY field?

eg.
name, surname, city
-> entered "mil"
-> result

Miller Martin New York
Rodemill Robert Washington
Fine Fran Milchester
Bond Emil Munich

Any hint for me to kick me into the right direction?

Thank you.

--------------------------------------
>>>>>> Bugs will appear in one part of a working program when another 'unrelated' part is modified <<<<<

 

[dhookom]

You can concatenate all the field values together so your where clause in your query might look like:

WHERE [FirstName] & [SurName] & [City] & [Address] Like "*" & Forms!frmSearch!txtFind & "*"

Duane
Hook'D on Access
MS Access MVP

 

[Golom]

There is, far as I know, no generic method to do this. For your specific example

Select * From SomeTable
Where NZ(name) & ' ' & NZ(surname) & ' ' & NZ(city) LIKE '*mil*'

Should do it.

I've inserted spaces between the fields so that you don't get things like
Cor[red]miL[/red]enNew York
returning a hit when it shouldn't.

 

[MrDataGuy]

If you are a ‘paranoid’ programmer (like myself) be careful of false positive matches. That is the above code example might find a match on a name ending with a ‘m’ and stating with a ‘il’ which is most likely not a match you wanted. (This assumes that the searched in text is fullt trimmed)

Two suggested modifications:

Change: “WHERE [FirstName] & [SurName] & [City] & [Address]”

To: WHERE [FirstName] & “ “ & [SurName] & “ “ & [City] & “ “ & [Address]”

-or-
chain into 3 searches.

Also beware that letter case can be a gotcha, you may want to change the text and search string to all uppercase. Or maybe letter case is important to the search so not ‘normalizing’ the text is the right thing to do


Lion Crest Software Services
Anthony L. Testi
President

http://www.LionCrestSoftwareServices.com

 

[dhookom]

Anthony (MrDataGuy),
Nice contribution to this thread and welcome to Tek-Tips
The OP might also want to try concatenate a rare character in place of the space like:

WHERE [FirstName] & "~" & [SurName] & "~" & [City] & "~" & [Address] ...

Duane
Hook'D on Access
MS Access MVP

 

[MrDataGuy]

Duane: Thank you for the welcome.

Like you I was thinking of using something other then a space, but I know enough of Access to know that there are special characters that have special meaning (like *) and I did not want to pick one that could introduce issues, so I went 'safe' with a space. I assume that you were trying to handle the situation where the user entered a two word search and could get the same issue that the space was trying to fix.

As I am far from an expert in Access I have a relatent to this thread question. If there are indexs on the Name, city etc. field would it not be better to do a 'chain' where clause for performace? Of course if the table is small it really does not matter.

Lion Crest Software Services
Anthony L. Testi
President

http://www.LionCrestSoftwareServices.com

 

[Golom]

Ideally you would want a character that could not be typed from the keyboard (unless of course the user knows about some obscure keyboard shortcuts.) To that end, do something like

WHERE [FirstName] & Chr$(7) & [SurName] & Chr$(7) & [City] & Chr$(7) & [Address] ...

I have arbitrarily selected ASCII code 7 (the BEL character) but you could use any number in the range 1-31 ... none of which have a keyboard equivalent.

 

[dhookom]

We have been designing the quick and dirty solution. Ideally, if I wanted greater performance using indexes, I would use code to build a where clause like:

  WHERE [FirstName] Like "*" & Forms!frmSearch!txtFind & "*"
  OR [SurName] Like "*" & Forms!frmSearch!txtFind & "*"
  OR [Address] Like "*" & Forms!frmSearch!txtFind & "*"

Duane
Hook'D on Access
MS Access MVP

 

[MajP]

The following will do what the original question asked. I assume you have a form and you want to return the records that match the value in any field. It is generic to work on any form. It could be beefed up a little to handle things like dates, and fields with single quotes.

Private Sub cmdFind_Click()
  Me.FilterOn = False
  Me.Filter = getAllFieldSearch(Me.RecordsetClone, Me.txtFind, "EmployeeID", True)
  MsgBox "filter " & Me.Filter
  Me.FilterOn = True
End Sub

Public Function getAllFieldSearch(rs As DAO.Recordset, strFind As String, PKfield As String, Optional numericPK As Boolean = False) As String
  'Parmaters
  'RS: Recordset to filter
  'strFind: The string to Find
  'PKField: The name of the primary key field
  'numericPK: True if the primary key is numeric
  Dim strOut As String
  Dim fld As DAO.Field
  Do While Not rs.EOF
     For Each fld In rs.fields
      If fld.Value Like "*" & strFind & "*" Then
         If strOut = "" Then
          If numericPK Then
            strOut = PKfield & " = " & rs.fields(PKfield)
          Else
            strOut = PKfield & " = '" & rs.fields(PKfield) & "'"
          End If
        Else
          If numericPK Then
            strOut = strOut & " OR " & PKfield & " = " & rs.fields(PKfield)
          Else
            strOut = strOut & " OR " & PKfield & " = '" & rs.fields(PKfield) & "'"
          End If
        End If
      End If
    Next fld
    rs.MoveNext
  Loop
  getAllFieldSearch = strOut
End Function

 

[kermit01de]

Thanks to all - I will try it in the next few days and come back.

Mirko

--------------------------------------
>>>>>> Bugs will appear in one part of a working program when another 'unrelated' part is modified <<<<<

 

[kermit01de]

Thanks MajP,

Great thing - working perfect.

--------------------------------------
>>>>>> Bugs will appear in one part of a working program when another 'unrelated' part is modified <<<<<

 

[MajP]

Although that proves you can make a generic function to query all fields, it is a pretty innefficient solution and should be only used in small databases. If you are talking thousands of records then you should use a more tailored solution.

However, here are a few improvements. My thought is that you would only use this for text searches. I can not imagine a search of multiple fields for a boolean or numeric value. So I designed it to skip non text fields. You could however modify the code so that if you entered a date it would check only date fields, or boolean check in boolean fields, or numeric check in numeric fields.

I did have a problem in that if it found the string in one field it still checked the other fields withou going to the next record. This gave a string like

empID = 1 or empID = 1 or empID = 2 or empID = 3

That has been fixed. However this whole thing may be limited to 99 records. There is a limit of 99 "Ands" in a where statement, I am not sure about "or".

Public Function getAllFieldSearch(rs As DAO.Recordset, ByVal strFind As String, PKfield As String, Optional numericPK As Boolean = False) As String
  'Parmaters
  'RS: Recordset to filter
  'strFind: The string to Find
  'PKField: The name of the primary key field
  'numericPK: True if the primary key is numeric
  'This really makes sense only for strings. I would not use
  'this to search for a date or number. So check only the text fields
  Dim strOut As String
  Dim fld As DAO.Field
  Do While Not rs.EOF
     For Each fld In rs.Fields
      'check on text fields
      If fld.Type = dbMemo Or fld.Type = dbText Then
         If fld.Value Like "*" & strFind & "*" Then
           If strOut = "" Then
              If numericPK Then
                strOut = PKfield & " = " & rs.Fields(PKfield)
                Exit For
              Else
                strOut = PKfield & " = '" & rs.Fields(PKfield) & "'"
                Exit For
             End If
           Else
             If numericPK Then
               strOut = strOut & " OR " & PKfield & " = " & rs.Fields(PKfield)
               Exit For
             Else
               strOut = strOut & " OR " & PKfield & " = '" & rs.Fields(PKfield) & "'"
               Exit For
             End If
           End If
         End If
      End If
    Next fld
    rs.MoveNext
  Loop
  getAllFieldSearch = strOut
End Function

Also if you are calling this mutliple times from a form, recommend before calling it you remove the filter
example

Private Sub testFind()
  Dim rs As DAO.Recordset
  Dim frm As Access.Form
  Dim strFind As String
  DoCmd.OpenForm "employees"
  Set frm = Forms("employees")
  
  strFind = InputBox("Input search")
  'remove the filter first
  frm.Filter = ""
  frm.FilterOn = False
  'After removing filter then set the recordset
  Set rs = frm.RecordsetClone
  EmployeeID ", True)"
  MsgBox "filter " & frm.Filter
  frm.FilterOn = True
End Sub

If you do not the second time you call it the recordset goes out of scope and you get an error. I do not fully understand why.

 

[kermit01de]

Ok, after I implemented the MajP solution may hopefully last question about that:

I am using the search in a dialog form, that displays the matching records in a list (parted form)

The search form has been called via a button from the main form, still open in the background.

After trying several things ... I am getting no more idea how to:

click on a record in the search form to
- close search form
- display record in main form.

Any hint for me?

--------------------------------------
>>>>>> Bugs will appear in one part of a working program when another 'unrelated' part is modified <<<<<

 

[MajP]

The way you describe it the pop up form shows the found records. So that tells me the pop up is designed only to be used for the specific form and not used for all forms. If that is the case then I have my recordset to work with since it is on the pop up. If I was designing this as a pop up for all forms then I would have to do some things different. See if this is what you mean. There are a few moving parts in what you describe so it is easier to demonstrate.

http://www.4shared.com/document/Le0WHIeI/SearchAllFields.html

The Employee form has a search button to pop open the employee search form.
The search form opens in dialog stopping the code in the calling form until the search form is hidden
The search form has a subform to show you the found records.
You get your filter string and filter the search form.
Then you hide the search form (OK)
The code resumes in the calling form
If the search form is hidden it pulls the search string and applies it to the calling form.

 

[kermit01de]

That comes quite near ... but what I want ist not a filtered main form showing all found items, but just the ONE, I selected in the popup - search - result. (dblclick)


--------------------------------------
>>>>>> Bugs will appear in one part of a working program when another 'unrelated' part is modified <<<<<

 

[kermit01de]

Got it in the meantime ...

Private Sub User_Click()
Form_Laptops!ID.Requery
Form_Laptops.RecordSource = Form_Laptops.RecordSource
Form_Laptops.Recordset.FindFirst ("[LfdNr] =" & Me.LfdNr)
DoCmd.Close acForm, Me.Name
End Sub


--------------------------------------
>>>>>> Bugs will appear in one part of a working program when another 'unrelated' part is modified <<<<<

 

[kermit01de]

Worked perfect thanks MajP - since -

- tried to find a string (eg. "milleringsen") that did not give matches - ok.
- entered another string to search (eg. "mil") that should give matches ---> did not find anything.

After closing and reopening entered the second string (eg. "mil") again and got the results as expected.

Do I have to reset something somewhere after "no results"?

Here is the implemented code where I already tried to avoid setting the filter with "no matches":

Private Sub txtFind_KeyDown(KeyCode As Integer, Shift As Integer)
If KeyCode = vbKeyReturn Then

KeyCode = 0
Me!txtFind = Me!txtFind.Text
Me.FilterOn = False
Flt = getAllFieldSearch(Me.RecordsetClone, Me.txtFind, "LfdNr", True)
If Len(Flt) <> 0 Then
Me.Filter = Flt
Me.FilterOn = True
End If
End If
End Sub



--------------------------------------
>>>>>> Bugs will appear in one part of a working program when another 'unrelated' part is modified <<<<<

 

[PHV]

In getAllFieldSearch I'd add the following line:
rs.MoveFirst
before that line:
Do While Not rs.EOF

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[kermit01de]

Thank you! That did it!

--------------------------------------
>>>>>> Bugs will appear in one part of a working program when another 'unrelated' part is modified <<<<<