CODEWITHSUNDEEP
ms-accessvba
bd528
bd528

Reputation: 886

Access VBA Criteria of AND and &

I am running the following query in Access VBA

    Case "Customer Name"
        strSQL = "SELECT CustomerID, Customer_Name, Industry FROM TBLCUSTOMERSNEW WHERE ucase(Customer_Name) Like '*" & UCase(tempStr) & "*'" & " ORDER BY Customer_Name"
        Me.lstSearchResults.ColumnCount = 4
        Me.lstSearchResults.ColumnWidths = "1cm;7cm;12cm;"

Is it possible to amend this query so that when the value being searched for (tempStr) contains a "&" the records returned also contain records where customer_name contains "and", and vice versa?

Upvotes: 0

Views: 79

Answers (1)

Erik A
Erik A

Reputation: 32642

Just thought up a simple alternative not using any IF logic for you.

SELECT   customerid, 
         customer_name, 
         industry 
FROM     tblcustomersnew 
WHERE    Replace(Ucase(customer_name), "&", "AND") LIKE '*" & Replace(UCase(tempStr), "&", "AND") & "*'" & " 
ORDER BY customer_name

That should do it.

Upvotes: 2

Related Questions