X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
   Reply to this topicStart new topic
> Search By Keywords, Access 2016    
post Aug 6 2017, 07:20 AM

Posts: 65
Joined: 15-October 03
From: New Zealand

Hi, I have been searching through the forum for hours and can't seem to find what I need.
I have a FAQ database - simple - questions and answers. I have a search form that works for one word. Using LIKE "*" & [forms]![frmSearch]![txtSearch] & "*"
This works perfect and brings up all the records with that one word in.
What I want is to be able to type in two or three words and the search will bring up only the records that contain all of the words.
I see how I can do it by adding other text boxes and using AND
But is there a way you can set it you so you can just type a couple of keywords with spaces or commas or something in between?

For example I have three records

Q1: How do I fix a broken wheel
Q2: How do I fix a broken bobbin
Q3: How do I fix a wheel
Q4: How do I fix a broken old wheel

at the moment I can search by "wheel" which will result in Q1 and Q3 records showing. Or I can search by "broken" and get records Q1 and Q2 and Q3.
But I want to search by "broken wheel" and results to show Q1 and Q4 - if I do the search now for "broken wheel" it will only show Q1 as Q4 has "old" in between?

Any advice greatly appreciated.
Go to the top of the page
post Aug 6 2017, 07:45 AM

Posts: 2,077
Joined: 27-February 09

Maybe something like this?

Public Function CreateFilter(ByVal strInput As String, ByVal strDelimiter As String)
Dim varList As Variant
Dim i As Integer

varList = Split(strInput, strDelimiter)
For i = LBound(varList) To UBound(varList)
If Len(CreateFilter) > 0 Then
CreateFilter = CreateFilter & " AND "
End If
CreateFilter = CreateFilter & " [Field] LIKE '*" & varList(i) & "*'"
Next i

End Function

The problem is that it may break up phrases that you don't want broken up.
Go to the top of the page
post Aug 6 2017, 07:56 AM

UA Admin
Posts: 30,163
Joined: 20-June 02
From: Newcastle, WA

Check the sample database on my website for potential ideas.

Go to the top of the page

Custom Search
RSSSearch   Top   Lo-Fi    16th August 2017 - 12:22 PM