Solved Multiple keyword search in ANY order? (1 Viewer)

theoret

New member
Local time
Today, 16:36
Joined
Feb 27, 2017
Messages
10
Hi everyone,

I'm hoping someone might be able to assist me with this small database I'm working on. I've attached a quick dry bones example of what I'm looking for and made so far.

Details
6 keyword buttons
1 column of data. These are file paths to .pdf files to be made in to hyperlinks later
1 sub form query in the main form that updates on keyword tab (for now).

For the most part, I have that working.

Issue
Ex: C:\ARCHIVE\NAME1 - C180 J487E 943 U777.pdf

If I put C180 as the first keyword and U777 as the second, this record shows up.

If I swap them and place U777 as the first keyword and C180 as the second, the record does not show up.

What I would like to happen
I would like to be able to search these records by keywords from the 6 text boxes, in any order.

I'm not yet comfortable scripting any VBA myself but have been able to follow along with other examples in the past if that's a route that's best. Would just need to be pointed in the right direction if you don't mind.

Edit: Here's what I have currently in my search criteria if that helps.

Like "*" & [Forms]![Search]![txtbox1] & "*" & "*" & [Forms]![Search]![txtbox2] & "*" & "*" & [Forms]![Search]![txtbox3] & "*" & "*" & [Forms]![Search]![txtbox4] & "*" & "*" & [Forms]![Search]![txtbox5] & "*"

Cheers,
 

Attachments

  • Search.accdb
    560 KB · Views: 262

pbaldy

Wino Moderator
Staff member
Local time
Today, 13:36
Joined
Aug 30, 2003
Messages
36,118
See if this is what you're after

1614383372513.png
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 16:36
Joined
May 21, 2018
Messages
8,463
I built something to make doing that easy and powerful. Would need minimal tweaking.
 

theoret

New member
Local time
Today, 16:36
Joined
Feb 27, 2017
Messages
10
See if this is what you're after

View attachment 89576
I appreciate the suggestion, though this didn't seem to work for me. After saving the query, Access placed them all on one line with OR LIKES but the search function would not pull results with any keywords afterwards.
 

pbaldy

Wino Moderator
Staff member
Local time
Today, 13:36
Joined
Aug 30, 2003
Messages
36,118
What was the SQL of the query? I tested and it appeared to work, but the site wouldn't let me post the SQL.
 

theoret

New member
Local time
Today, 16:36
Joined
Feb 27, 2017
Messages
10
What was the SQL of the query? I tested and it appeared to work, but the site wouldn't let me post the SQL.
Sorry,

edit:

SELECT Index.Location

FROM [Index]

WHERE (((Index.Location) Like "*" & [Forms]![Search]![txtbox1] & "*" Or (Index.Location) Like "*" & [Forms]![Search]![txtbox2] & "*" Or (Index.Location) Like "*" & [Forms]![Search]![txtbox3] & "*" Or (Index.Location) Like "*" & [Forms]![Search]![txtbox4] & "*" Or (Index.Location) Like "*" & [Forms]![Search]![txtbox5] & "*" Or (Index.Location) Like "*" & [Forms]![Search]![txtbox6] & "*"));
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 16:36
Joined
May 21, 2018
Messages
8,463
If you want to try that form I provided. You just need to change the rowsource of the listbox to show the location.
Then modify this code
End Sub
Code:
Private Sub Search()
  Dim strRowSource As String
  Dim strSearch As String
  Dim StrSql  As String
  strSearch = GetSearch
  If strSearch <> "" Then
    strRowSource = "SELECT Companies.CompanyName FROM Companies Where " & strSearch & " ORDER BY Companies.CompanyName"
    Me.lstAllrecords.RowSource = strRowSource
    Me.txtFilter = strSearch
   Else
     Me.txtFilter = ""
     Me.lstAllrecords.RowSource = OriginalSql
  End If
End Sub
to
strRowSource = "SELECT Location FROM [Index] Where " & strSearch & " ORDER BY Location"
Then add a click event to the listbox to go to that record

FYI. INDEX is a very bad name for a table. It is likely a reserved word and very confusing. Rename tblIndex or something.
 

pbaldy

Wino Moderator
Staff member
Local time
Today, 13:36
Joined
Aug 30, 2003
Messages
36,118
Curious, mine leaves them looking like the above. Did you type just the individual lines, one into each OR box?

Like "*" & [Forms]![Search]![txtbox1] & "*"
Like "*" & [Forms]![Search]![txtbox2] & "*"
Like "*" & [Forms]![Search]![txtbox3] & "*"
Like "*" & [Forms]![Search]![txtbox4] & "*"
Like "*" & [Forms]![Search]![txtbox5] & "*"
 

bastanu

AWF VIP
Local time
Today, 13:36
Joined
Apr 13, 2010
Messages
1,401
Please have a look at the attached.
Cheers,
Vlad
 

Attachments

  • SearchVlad.zip
    72.5 KB · Views: 267

theoret

New member
Local time
Today, 16:36
Joined
Feb 27, 2017
Messages
10
If you want to try that form I provided. You just need to change the rowsource of the listbox to show the location.
Then modify this code
End Sub
Code:
Private Sub Search()
  Dim strRowSource As String
  Dim strSearch As String
  Dim StrSql  As String
  strSearch = GetSearch
  If strSearch <> "" Then
    strRowSource = "SELECT Companies.CompanyName FROM Companies Where " & strSearch & " ORDER BY Companies.CompanyName"
    Me.lstAllrecords.RowSource = strRowSource
    Me.txtFilter = strSearch
   Else
     Me.txtFilter = ""
     Me.lstAllrecords.RowSource = OriginalSql
  End If
End Sub
to
strRowSource = "SELECT Location FROM [Index] Where " & strSearch & " ORDER BY Location"
Then add a click event to the listbox to go to that record

FYI. INDEX is a very bad name for a table. It is likely a reserved word and very confusing. Rename tblIndex or something.

I've taken a quick look at your database and I can certainly learn some things here. Thank you for providing those details. I'll be sure to change the table name as well.
 

theoret

New member
Local time
Today, 16:36
Joined
Feb 27, 2017
Messages
10
Curious, mine leaves them looking like the above. Did you type just the individual lines, one into each OR box?

Like "*" & [Forms]![Search]![txtbox1] & "*"
Like "*" & [Forms]![Search]![txtbox2] & "*"
Like "*" & [Forms]![Search]![txtbox3] & "*"
Like "*" & [Forms]![Search]![txtbox4] & "*"
Like "*" & [Forms]![Search]![txtbox5] & "*"
Yep, just like that. I had 6 lines, saved it, opened it back up and it was one. bastanu's attachment does the trick though!
 

theoret

New member
Local time
Today, 16:36
Joined
Feb 27, 2017
Messages
10
Please have a look at the attached.
Cheers,
Vlad

This seems to be working great from my tests. Thank you for assisting with this.

I appreciate everyone who took the time to respond.
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 16:36
Joined
May 21, 2018
Messages
8,463
INDEX is on the list as you would expect. Anything that could be an Access object is a bad choice or something used in SQL (table, field, index, Select, Text, value, query etc.)
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 04:36
Joined
May 7, 2009
Messages
19,169
maybe this one:

...
WHERE ((([Location]) Like "*" & Nz([Forms]![Search]![txtbox1],"@#$%") & "*")) OR ((([Location]) Like "*" & Nz([Forms]![Search]![txtbox2],"@#$%") & "*")) OR ((([Location]) Like "*" & Nz([Forms]![Search]![txtbox3],"@#$%") & "*")) OR ((([Location]) Like "*" & Nz([Forms]![Search]![txtbox4],"@#$%") & "*")) OR ((([Location]) Like "*" & Nz([Forms]![Search]![txtbox5],"@#$%") & "*"));
 

Users who are viewing this thread

Top Bottom