Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

Search Separate Terms In A Form

Discussion in 'Information Technology' started by Archie, Jul 28, 2006.

  1. Archie

    Archie
    Expand Collapse
    Guest

    I created a unbound textbox to search one of the tables, it works well for
    one search.
    But the problem is usually, users would type in more than one search terms,
    and I would like to make it possible to retrieve records if any of the search
    term matches.

    For example, if I have a name record, "Thomas Smith", for now, it is okay
    for me to type in "Thomas" or "Smith" to get the record.

    What I want is if users type in "Thomas Johnson" or "Joe Smith", they can
    still retrieve the record "Thomas Smith". Is it possible to do that?

    My search code has been:
    Like "*" & Me.[textbox] & "*"

    Thanks a lot!
     
  2. Loading...

    Similar Threads Forum Date
    Gurbani Search Engine Literature Jun 12, 2017
    Sikh News Indian American Couple Donates For Sikh, Punjabi Culture Research In US Breaking News Aug 19, 2016
    Soul Search By Swarn Singh Bains Book Reviews & Editorials May 25, 2016
    Harpal Singh Kumar Cancer Researcher Knighted By Queen Elizabeth II Sikh Personalities Jan 5, 2016
    Movies Ashdoc's Movie Review---dozakh In Search Of Heaven ( 2015 ) Theatre, Movies & Cinema Apr 6, 2015

  3. OP
    Jeff L

    Jeff L
    Expand Collapse
    Guest

    You cannot do this with a simple query. You will need some coding to
    do it. Here is something I have used.

    Dim FilterString As String, Position As Integer, DescriptionFilter As
    String, HoldDescriptionFilter As String

    If Not IsNull(Me.FilterDescription) Then
    HoldDescriptionFilter = Me.FilterDescription
    Position = InStr(1, HoldDescriptionFilter, ",",
    vbTextCompare)
    If Position > 0 Then 'There is more than one word to look
    for in the description
    Do Until Position = 0
    DescriptionFilter = IIf(DescriptionFilter = "",
    "Description like '*" & _
    Left(HoldDescriptionFilter, Position - 1) & "*' ",
    DescriptionFilter & "Or Description like '*" & _
    Left(HoldDescriptionFilter, Position - 1) & "*' ")
    HoldDescriptionFilter = Trim(Mid(HoldDescriptionFilter,
    Position + 1))
    Position = InStr(1, ",", HoldDescriptionFilter,
    vbTextCompare)
    Loop
    DescriptionFilter = DescriptionFilter & "Or Description
    like '*" & HoldDescriptionFilter & "%' "
    FilterString = IIf(FilterString = "", DescriptionFilter, _
    FilterString & "And (" &
    DescriptionFilter & ")")
    Else

    FilterString = IIf(FilterString = "", "Description Like '*"
    & Me.FilterDescription & "*'", _
    FilterString & "And Description Like '*" &
    Me.FilterDescription & "*'")
    End IF
    Me.Filter = FilterString
    Me.FilterOn = True

    Else
    Me.FilterOn = False
    End IF
     
  4. OP
    Archie

    Archie
    Expand Collapse
    Guest

    Thanks very much!
    I will have a try on this!

    "Jeff L" wrote:

    > You cannot do this with a simple query. You will need some coding to
    > do it. Here is something I have used.
    >
    > Dim FilterString As String, Position As Integer, DescriptionFilter As
    > String, HoldDescriptionFilter As String
    >
    > If Not IsNull(Me.FilterDescription) Then
    > HoldDescriptionFilter = Me.FilterDescription
    > Position = InStr(1, HoldDescriptionFilter, ",",
    > vbTextCompare)
    > If Position > 0 Then 'There is more than one word to look
    > for in the description
    > Do Until Position = 0
    > DescriptionFilter = IIf(DescriptionFilter = "",
    > "Description like '*" & _
    > Left(HoldDescriptionFilter, Position - 1) & "*' ",
    > DescriptionFilter & "Or Description like '*" & _
    > Left(HoldDescriptionFilter, Position - 1) & "*' ")
    > HoldDescriptionFilter = Trim(Mid(HoldDescriptionFilter,
    > Position + 1))
    > Position = InStr(1, ",", HoldDescriptionFilter,
    > vbTextCompare)
    > Loop
    > DescriptionFilter = DescriptionFilter & "Or Description
    > like '*" & HoldDescriptionFilter & "%' "
    > FilterString = IIf(FilterString = "", DescriptionFilter, _
    > FilterString & "And (" &
    > DescriptionFilter & ")")
    > Else
    >
    > FilterString = IIf(FilterString = "", "Description Like '*"
    > & Me.FilterDescription & "*'", _
    > FilterString & "And Description Like '*" &
    > Me.FilterDescription & "*'")
    > End IF
    > Me.Filter = FilterString
    > Me.FilterOn = True
    >
    > Else
    > Me.FilterOn = False
    > End IF
    >
    >
     
Since you're here... we have a small favor to ask...     Become a Supporter      ::     Make a Contribution     


Share This Page