Can Access match words in any order in a query? (1 Viewer)

[AndrewS]

I have implemented a search-as-you-type function on a combobox on a form displaying company details, so that a user can can start typing an organisation's name and get a drop down of matches, from which they can pick the one they want, and that organisations details are displayed.

That works. No problem.

But only as long as the user types in the organisation name in the order of the words in the record.

As an example:

The field contains "University of Cambridge"

If the user types "uni" the list of matches will include "University of Cambridge"
If the user types "camb" the list of matches will also include "University of Cambridge"

But if the user types "Uni Cambridge" or "Cambridge Uni" then it is not matched.

Is it possible in Access to create a query to produce matches for the criteria input by the user in any order, or is that something that's only possible in full-scale SQL server?

 

[Ranman256]

youd need vb to chop up the keywords, then build the WHERE clause below


SELECT NAME FROM tClients
WHERE
((tClients.LastN) Like "*UNI*") OR
((tClients.LastN) Like "*CAMB*")

 

[plog]

I don't what your matching code looks like, but when you want to find a string within another string regardless of location you could use 2 methods:

InStr() - https://www.techonthenet.com/access/functions/string/instr.php
VBA solution and if your search string is anywhere in the main string it returns a positive number, if not 0 is returned.

LIKE with wildcard - [YourFieldHere] LIKE "*YourSearchHere*"
SQL solution and finds your search string anywhere within your field

 

[AndrewS]

Thanks Ranman256,

That was the prompt I needed.

Here's what I've come up with and on a quick initial test, it seems to be working

Private Sub cboFindOrganisationName_Change()

   
  Dim strAllRecords As String
  Dim strFilteredRecords As String
  Dim strMultiPart() As String
  Dim strTextEntered As String
  Dim lngWordCount As Long
  Dim lngCounter As Long
  
 
  Me.cboFindOrganisationName.SetFocus
  strAllRecords = "SELECT tbl_Organisations.ID, tbl_Organisations.OrganisationName " & _
                  "FROM tbl_Organisations ORDER BY tbl_Organisations.OrganisationName;"
  strFilteredRecords = "SELECT tbl_Organisations.ID, tbl_Organisations.OrganisationName " & _
                       "FROM tbl_Organisations WHERE tbl_Organisations.OrganisationName " & _
                       "LIKE ""*" & Me.cboFindOrganisationName.Text & "*"" " & _
                       "ORDER BY tbl_Organisations.OrganisationName;"
                       
  strTextEntered = Me.cboFindOrganisationName.Text
  strMultiPart = Split(strTextEntered, " ")
  lngWordCount = 0
  If InStr(1, strTextEntered, " ") <> 0 Then
    lngWordCount = UBound(strMultiPart)
    strFilteredRecords = "SELECT tbl_Organisations.ID, tbl_Organisations.OrganisationName " & _
                         "FROM tbl_Organisations " & _
                         "WHERE "
    For lngCounter = 0 To lngWordCount
      If lngCounter = 0 Then
        strFilteredRecords = strFilteredRecords & _
                            "tbl_Organisations.OrganisationName LIKE ""*" & strMultiPart(lngCounter) & "*"" "
      Else
        strFilteredRecords = strFilteredRecords & _
                             "AND tbl_Organisations.OrganisationName LIKE ""*" & strMultiPart(lngCounter) & "*"" "
      End If
    Next lngCounter
    strFilteredRecords = strFilteredRecords & _
                         "ORDER BY tbl_Organisations.OrganisationName;"
  End If
                       
                       
                       
   fLiveSearch Me.cboFindOrganisationName, Me.cboFindOrganisationName, strAllRecords, strFilteredRecords

 
End Sub

Needs further testing, but looks encouraging.