Multiple keyword search on 1 column (1 Viewer)

King21

New member
Local time
Today, 20:43
Joined
Jun 14, 2021
Messages
18
I am trying to do a multiple search on addresses that are in 1 column.

Example:
Column "Address"
18 NEAGLES ROCK RD, LAGUNA

I would like to be able to search for: NEAGLES and LAGUNA i.e. the Street or Road name and the suburb.
I can search on whole address, street name or suburb name, which gives me the desired results, but I need to search for street and suburb names ignoring the street, road, or lane etc.

I know that having the complete address in 1 column is not normalization, but this table is copied and imported from a corporate web page.

Is there a method of searching a single column with 2 keywords.

My search button code is:

strSearch = "SELECT Customers.ID, Customers.OrderDate, Customers.OrderNo, Customers.Customer, Customers.Address, Payment.Payment " _
& "FROM Customers LEFT JOIN Payment ON Customers.OrderNo = Payment.PayNo " _
& "Where [OrderDate] Like '*" & Me.txtcriteria & "*' " _
& " OR [OrderNo] Like '*" & Me.txtcriteria & "*' " _
& " OR [Address] Like '*" & Me.txtcriteria & "*' " _
& " OR [Customer] Like '*" & Me.txtcriteria & "*' " _
& "ORDER BY Customers.[OrderDate] Desc "
 

Attachments

  • Search-1A.accdb
    812 KB · Views: 247

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 18:13
Joined
May 7, 2009
Messages
19,175
check the code.
 

Attachments

  • Search-1A.accdb
    840 KB · Views: 259

King21

New member
Local time
Today, 20:43
Joined
Jun 14, 2021
Messages
18

arnelgp

Thanks for the reply, your code works well.

Can the code be refined so that the only results are exact matches.

Your code gives many results:
If I search for NEAGLES and LAGUNA, it gives all addresses with LAGUNA
or if I search for MINTARO and CATTAI it gives 2 results : 26 MINTARO RD, CATTAI and 78 MINTARO RD, DURAL

Is it possible that if I search for MINTARO and CATTAI that I get 1 result: 26 MINTARO RD, CATTAI
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 18:13
Joined
May 7, 2009
Messages
19,175
Replace OR with AND on the code.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 06:13
Joined
Feb 19, 2002
Messages
42,981
strSearch = "SELECT Customers.ID, Customers.OrderDate, Customers.OrderNo, Customers.Customer, Customers.Address, Payment.Payment " _
& "FROM Customers LEFT JOIN Payment ON Customers.OrderNo = Payment.PayNo " _
& "Where [OrderDate] Like '*" & Me.txtcriteria & "*' " _
& " OR [OrderNo] Like '*" & Me.txtcriteria & "*' " _
& " OR ([Address] Like '*" & Me.txtcriteriaStreet & "*' AND [Address] Like '*" & Me.txtcriteriasuburb & "*' ") _
& " OR [Customer] Like '*" & Me.txtcriteria & "*' " _
& "ORDER BY Customers.[OrderDate] Desc "

The parentheses are necessary to ensure that the logical operators are evaluated as you intext. So BOTH parts of the address must be true.
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 06:13
Joined
May 21, 2018
Messages
8,463
You may find this interface interesting
 

King21

New member
Local time
Today, 20:43
Joined
Jun 14, 2021
Messages
18
Thanks for the replies

Pat

Changed the "Address" line of code as you suggested, I get "Compile Error" Expected: End of Statement and it highlights the right parentheses
I have tried several methods (comas, parentheses, braces etc.) can't get it to work.

Any suggestions

MajP

Looks good, I'll have a closer look later

arnelgp

Change which OR
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 18:13
Joined
May 7, 2009
Messages
19,175
here is you Exact search.
 

Attachments

  • Search-1A.accdb
    688 KB · Views: 250

juke2huke

New member
Local time
Today, 15:13
Joined
Jul 29, 2021
Messages
4
URL redirection We recognize that the address bar is the only reliable security indicator in modern browsers; consequently, we hold that the usability and security benefits of a small number of well-designed and closely monitored redirectors outweigh their true risks
 

Users who are viewing this thread

Top Bottom