multiple search in a single field

[SusanSerbey]

Hi all
I have a table in MS access containing drugs and another table containing drugs composition (component).
in my search form, i have the possibility to search, in my drug database, for a particular component.
How can I sear for 2 different components in a single drug without creating a seconf field for component search,
because sometimes i will need to search for a drug containing 3 or more components.
My solution was to convert my actual search field for component to a search with checkboxes where i can choose the different components i am looking for

anyone can help?

[ADezii]

1. I do have a strictly code-based solution whereas you can search for as many Components within a Drug as you like. All the Components to search for can be listed in a single Text Box in a Delimited Format such as listed below. You can also specify your Delimiter between Components:
   

   Component A,Component G,Component Q,Component Z

2. This approach has a very strict syntax requirement and the Components to search for much match exactly with those in a Components Table should they exist.
3. A SQL-based approach would be much better should one exist, so I'll simply wait and see what suggestions are offered prior to posting mine.

[PhilOfWalton]

I think this might be of help

Under Available Services there are 2 columns, left hand one is the definition and right hand one is the description.
At the bottom is a label saying "Enter a few letters of the service required (use a tilde ~ to separate words)"
To the right of that I have typed ca~cre.

This will search in both the definition and the description for words containing the letters "ca" or "cre"
Very flexible, very fast.

Phil

[SusanSerbey]

Can't wait ADezii, we tried a lot of solutions, but the result of the search was always wrong
I will wait yours

[SusanSerbey]

Thank you Phil,
we will try it early tomorow morning and be back to you

[ADezii]

1. I'm assuming that there can be literally thousands or tens-of-thousands of Drugs and their associated Components. For this reason, I decided not to go the List Box approach where you can selectively choose any number of Components among the thousands. If I am incorrect the Logic can easily be modified to incorporate a Multi-Select List Box or similar functionality whereas you can choose any number of Components among the thousands to test for.
2. For the sake of simplicity I have created a simple One ==> MANY Relationship between Drugs and their Components but in reality this is not true. Since a single Drug can contain many Components, and a single Component can be contained in many Drugs, a MANY to MANY Relationship actually exists between the two. Again, this can be modified later on.
3. I went with your original idea of listing one or more Components in a single Text Box on a Form then querying to see which Drugs, if any, contain ALL of the Components. I listed the Components in a Delimited Format using a ',' as the Default, such as:
   

   Component A,Component B,Component C, etc...

4. You can change the Delimiter if you like by simply changing it in the Constant
   

   conDELIMITER

5. I will not bore you with the details and Logic, simply download the Attached Demo. It should be self explanatory, but if you have any additional questions or comments, we are here to assist you.
6. P.S. - All the Code exists in a simple Event Procedure for the sake of simplicity.

[PhilOfWalton]

Assuming you have a form with 2 columns one called drugs and one component, add a textbox "TxtFilter" in the footer, as per my image.

Try the following code

Sub FilterForm()
 
    Dim Fltr As String
 
    If SelectMediaType > 0 Then                         ' Media Type not all
        Fltr = "MediaTypeID = " & SelectMediaType
    End If
 
    ' If we return no records, tempotarily remove the filter
    If Me.RecordsetClone.RecordCount = 0 Then
        Me.FilterOn = False
    End If
 
    TxtFilter.SetFocus
 
    If Left(TxtFilter.Text, 1) = "~" Then                ' Don't allow a tilde as first letter
        TxtFilter.Text = ""
    End If
 
    Fltr = "(Drugs Like '*" & Replace(Me.TxtFilter.Text, "'", "''") & "*'"
    Fltr = Replace(Fltr, "~", "*' OR Drugs Like '*")
    Fltr = Fltr & " OR Component Like '*" & Replace(Me.TxtFilter.Text, "'", "''") & "*'"
    Fltr = Replace(Fltr, "~", "*' OR Component Like '*")
    Fltr = Fltr & ")"
 
    Me.Form.Filter = Fltr
    Me.FilterOn = True
 
 
    Me.TxtFilter.SetFocus
 
    ' Move the cursor to the end of the input text box.
    If Me.RecordsetClone.RecordCount = 0 Then
        Exit Sub
    End If
 
    Me.TxtFilter.SelStart = Len(Me.TxtFilter.Text)
 
End Sub
 

Phil

[NeoPa]

As I assume each drug can have multiple components and each component can be used in multiple drugs there will no doubt be a cross-referencing table that lists the connections between drugs and components. I don't know what your table's called so I'll simply call it [DrugComponent] for now and we may change it later when we get more information from you.

If we're interested in filtering directly then we need a set of data that includes both the drug and a list of the components. This is not straightforward and cannot be done leaving the resulting data editable. Nevertheless, Combining Rows-Opposite of Union gives a way it can be done.

With a form or report built on this data, which has a single record per drug and a field we'll call [ComponentList] for now, we can filter that data based on a filter string taken from a TextBox on the same or another form. We'll call that [txtFilter] for now. The AfterUpdate event procedure of the TextBox (I'll assume this is in the header of the same form used for showing this list of drugs in the Detail section for now.) can then be written to build a filter string that will allow each matching drug record to show.

Private txtFilter_AfterUpdate()
    Dim strWhere As String, strSep As String
    Dim astrComponent() As String
    Dim varComponent As Variant
 
    strSep = ","
    With Me
        astrComponent = Split(.txtFilter, strSep)
        For Each varComponent In astrComponent
            strWhere = strWhere _
                     & " OR ([ComponentList] Like '*" & varComponent & "*')"
        Next varComponent
        strwhere = Mid(strWhere, 5)
        If strWhere <> .Filter Then .Filter = strWhere
        .FilterOn = (.Filter > "")
    End With
End Sub