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 "
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 "