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
    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
    Research Shows NFL Quarterbacks Play Better With Beards Business, Lifestyle & Leisure Nov 23, 2014

  3. 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. 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
    >
    >
     

Share This Page