Multiple keyword search on 1 column

[King21]

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 "

 

[arnelgp]

check the code.

 

[King21]

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]

Replace OR with AND on the code.

 

[Pat Hartman]

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 may find this interface interesting

Solved - Search all words in field against all words in another field

I kind of disagree in a db because you can probably build a much better search using sql Like search. This is based on a pretty thorough demonstration of a couple of interfaces using (Soundex, Simil, Levenshtein, and just find as you type) Like?? Like won't find similarities in strings with...

www.access-programmers.co.uk

 

[King21]

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]

here is you Exact search.

 

[King21]

arnelgp​

Thanks for the reply

Works like a charm, thank you

 

[juke2huke]

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